Alistair has attended:
Excel Advanced course
Negative time
Hi,
I now wish to take one time away from another. And in this case the aswer is a negative time.
eg: 8:30-8:44 should be -0:14, but is diplayed as ######## (or -0.00972 when I paste values).
Anyway around this? I am using the [hh]:mm formating.
Cheers
ALi.
RE: negative time
it must be 08:44-08:30 not 08:30-08:44
RE: negative time
Ali,
There are several ways around this. As they can be fairly technical, and specific to what you are looking for, I'd rather not just retype them all below in an attempt to seem intelligent. So here is a way for you to get what you want:
Search for the following in Google:
excel negative time
have a look at how several of the solutions have been created. one of them should fit for you.
Let me know how you get on.
Hope you enjoyed your Microsoft Excel training the other day.
Richard
RE: negative time
Hi Alistair,
Try this one, (Use your format in all the active cells but just use one h in square brackets to show the 24 hour format, in Format Cells, Number, Custom remove any reference to :ss for seconds.)
To fix your problem you will need to use an IF function to add a day to the minus time if it is necessary.
Probably easier if I put in the formula so that I can explain it for you...
My start time is in B5 and my end time is in C5, my start time is for example 20:00 and my end time the following day at 02:00, Excel will consider that I have started after I finished, (not good) however adding an extra day to your formula will allow Excel to provide you with the correct period of working. The formula which will provide you with a correct answer in cell C7 (for example) should read as follows: select C7 and enter =IF(C5<B5,C5+1-B5,C5-B5)you can then copy this formula to other cells if necessary.
RE: negative time
Hi Pete,
the answer now shows as 23:42 instead of -0:18. Certainly a more usable figure but still not what I'm looking for. I shall have a playaround putting another if function around it and perhaps displaying it in some other format.
Cheers
ALi.
RE: negative time
Hi Alistair,
How did you get on with this? Are you still trying to find the solution?
If you've found one, I'm sure readers of this forum would like to know the answer.
Please either mark this question as 'resolved' or let us know if you require further assistance.
Kind regards,
Rich
RE: negative time
Hi Rich,
I have re-jigged the spreadsheet so -ve time (but show as +ve) is on one row and +ve time on another, making the figures workable.
Unfortunately I haven't the time to play around with it this month to find any ultimate solution. I will mark this query as resolved, but should I find a solution at a later date, I will be sure to post it.
Cheers
ALi.
