advanced excel seminar training - negative time

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » advanced excel seminar training - Negative time

advanced excel seminar training - Negative time

resolvedResolved · Low Priority · Version Standard

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

Edited on Fri 16 Mar 2007, 14:22

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.


 

Excel tip:

Quickly copy a formula across sheets

Suppose you have a formula in cell Sheet1!B2, say =A1*5%, that you wish to copy to cell B2 on Sheet2, Sheet3 and Sheet4. Instead of using copy and paste, try this: (1) Select Sheet1!B2. (2) Group Sheet1 with the worksheets Sheet2, Sheet3 and Sheet4 by holding down Ctrl and clicking on the tabs of the sheets to group them. (3) Press the F2 key, then immediately press Enter to copy the formula in Sheet1!B2 across the grouped sheets.

Remember to ungroup the sheets afterwards! Right-click on any tab and choose Ungroup Sheets to do that.

View all Excel hints and tips


Server loaded in 0.09 secs.