Hi Tony, I'm not 100% clear what you want to display. For instance, for -0.75 minutes do you want -0 and -45 or -0 and 45 or 0 and -45? Or, doesn't it matter much so long as any non-zero figure has the correct sign?
Also, for -4.483 do you want -4 and -28.98 or do you want the seconds rounded to, say, the nearest second? The following assumes you do want rounding to the nearest second. Omit the ROUND functions if you don't. I don't use Excel, I use Open Office, but am pretty sure the following works in both: Assuming cell A1 has the number you want to convert, then to get whole minutes use: =ROUNDDOWN(ROUND(A1*60)/60) (Note that ROUNDDOWN rounds towards zero, which is what you want.) Assuming you put the whole minutes in cell B1, then seconds are: =ROUND(60*(A1-B1)) For -0.75 minutes this will give 0 and -45 For -1.25 minutes it will give -1 and -15 For 2.999 minutes it will give 3 minutes 0 seconds. For -4.483 minutes it will give -4 and -29 I don't know any simple way to display -0. It's much easier to show 0. Hope this helps Best wishes Chris ----- Original Message ----- From: "Tony Moss" <[EMAIL PROTECTED]> To: "Sundial Mailing List" <[email protected]> Sent: Monday, August 25, 2008 10:35 AM Subject: Transfer to EXCEL > Fellow Shadow Watchers, > For many years now all my > dialling calculations have been done using spreadsheets in an ancient > version of MicroSoft 'Works'. A recent change of Mac operating system > has me painfully transferring everything to EXCEL version 12.1.1 > Data cells COPY/PASTE directly but some of the 'Works' formulae no > longer function and 'Help' is pretty convoluted. > > I need to take positive and negative decimal times e.g. 3.26 minutes and > -4.483 minutes and convert them into two columns of whole minutes and > whole seconds. As I recall negative values of less than 1 minute e.g > -0.816 mins. were a particular problem. > > As one who has to take his shoes off for numbers greater than 10 :-) > any assistance would be much appreciated. > > Tony Moss > --------------------------------------------------- > https://lists.uni-koeln.de/mailman/listinfo/sundial > --------------------------------------------------- https://lists.uni-koeln.de/mailman/listinfo/sundial
