Help with Time Calculations

  • Thread starter Thread starter Michael Walsh
  • Start date Start date
M

Michael Walsh

I'd be very grateful for some help with Excel Time calculations.

I can't find out how to show, and calculate with, a negative time value, ie
time owed.

It seems no less logical to want to be able to show time owed than it does
money owed and yet Excel seems not to allow it. Can anyone tell me how I
might do this please?

Any help with this problem would be greatly appreciated.

Thanks in anticipation.
Mick
 
Hi Mick,

Normally Excel can't handle negative times. The starting point for Excel's
date/time systen=m (really one system) was 1-1-1900.
For Apple macintosh, there was a system starting at 1-1-1904 (this avoided
the naive error for leap years in 1900). This system can also be chosen in
Windows and it gives you some room for negative times. Be aware thet it
affects all your workbookd; read HELP for further details.
Tools>Options>Calculation, check 1904 Date System

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
That's brilliant Niek.

When I read your reply I thought you'd misunderstood my question because I
couldn't see how changing the base date would make any difference,
especially as I'm only working in hours and minutes, but I've tried it and
it does the trick.

How does that work then?

Are there any disadvantages in general use of having this option ticked?

Thanks again.

Regards,
Mick
 
Hi Mick,

I assume this system gives some 4 years room for negative times/dates.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Michael
Be careful using the 1904 date system. If someone else opens your workbook, and has thier date system set to 1900, all of the dates will be off by 4 years

Chip Pearson gives a great explanation of how Excel handles time at
http://www.cpearson.com/excel/datetime.htm#AddingTime

By the way, the leap year error in the 1900 time system was intentional. It was done to be compatable with Lotus 123, where it was an actual error (reference Chip Pearson)

Regards
Mark Graesse
(e-mail address removed)

----- Michael Walsh wrote: ----

That's brilliant Niek

When I read your reply I thought you'd misunderstood my question because
couldn't see how changing the base date would make any difference
especially as I'm only working in hours and minutes, but I've tried it an
it does the trick

How does that work then

Are there any disadvantages in general use of having this option ticked

Thanks again

Regards
Mic
 
Hi Mark,

Thanks for the warning cos the file in question is likely to be used by
several people.

The link is very useful and interesting and I'm working my way through it
for some ideas.

The thing I'm trying to do, by the way, is to computerise flexi sheets at
work and, as time can be owed under this system (ie people can work less
than a full day one day and then make it up over a period of time), I need
to be able to use negative time values.

Many Thanks.
Michael

Mark Graesser said:
Michael,
Be careful using the 1904 date system. If someone else opens your
Click to expand...
workbook, and has thier date system set to 1900, all of the dates will be
off by 4 years.
Chip Pearson gives a great explanation of how Excel handles time at:
http://www.cpearson.com/excel/datetime.htm#AddingTimes

By the way, the leap year error in the 1900 time system was intentional.
Click to expand...
It was done to be compatable with Lotus 123, where it was an actual error
(reference Chip Pearson).
 
Hi Mark,

Thanks for the warning cos the file in question is likely to be used by
several people.

The link is very useful and interesting and I'm working my way through it
for some ideas.

The thing I'm trying to do, by the way, is to computerise flexi sheets at
work and, as time can be owed under this system (ie people can work less
than a full day one day and then make it up over a period of time), I need
to be able to use negative time values.

Many Thanks.
Michael
Click to expand...

Michael,

Even with the 1900 date system, the "negative times" are still in the cell, so
to speak. The limitation is in either entering the data directly (e.g. -1:00
will flag an error) or displaying them when they are formatted as TIME.

Also recall that EXCEL stores times as fractions of a day, so you can convert
the TIME to a decimal number of hours by multiplying by 24.

So, for example if you had

A1: 8:00
A2: 6:00

B1: =A2-A1

B1 would display ######## but would contain the number -0.8333333333
B1 * 24, formatted as GENERAL, would show -2

I don't know if this information will help in your worksheet.




--ron
 
Ron Rosenfeld said:
Michael,

Even with the 1900 date system, the "negative times" are still in the cell, so
to speak. The limitation is in either entering the data directly (e.g. -1:00
will flag an error) or displaying them when they are formatted as TIME.

Also recall that EXCEL stores times as fractions of a day, so you can convert
the TIME to a decimal number of hours by multiplying by 24.

So, for example if you had

A1: 8:00
A2: 6:00

B1: =A2-A1

B1 would display ######## but would contain the number -0.8333333333
B1 * 24, formatted as GENERAL, would show -2

I don't know if this information will help in your worksheet.




--ron
Click to expand...

Hi Ron,

I'll give the method you described a try.

Thanks again for your help.

Best Regards,
Michael
 
Micheal

One more option for you. You can fake out the cell to show a negative time. The following formula will display the negative time but the actual value in the cell will be text. This will prevent you from using this cell as a component in any other formula

=IF(B1-A1>=0,B1-A1,"-"&TEXT(ABS(B1-A1),"[h]:mm")

The above IF statement checks to see if B1 minus A1 is not negative, if it isn't negative, it returns the number, if it is negative, it will return a text value of a negative sign concatenated to the absolute value of the time calculation

If you do need to use this value in another formula, assuming the above formula is in C1, then just substitute (B1-A1) in any formula where you need C1

This will allow you to stay in the 1900 system and display negative times. Though this particular cell will display a text value when the number is negative you can still use the actual value in other calculation by substituting the formula

Good Luck
Mark Graesse
(e-mail address removed)

----- Michael Walsh wrote: ----


Ron Rosenfeld said:
On Sat, 14 Feb 2004 10:47:29 -0000, "Michael Walsh
cell, s
to speak. The limitation is in either entering the data directl (e.g. -1:0
will flag an error) or displaying them when they are formatted as TIME conver
the TIME to a decimal number of hours by multiplying by 24
B1 * 24, formatted as GENERAL, would show -
Click to expand...

Hi Ron

I'll give the method you described a try

Thanks again for your help

Best Regards
Michae
 
Hi!

You try all ready the next formula?
=DATEDIF(A1,TODAY(),"d")
at the end of this formula you can use the sign / according to the
decimals you need where A1 is gonna be the reference, also you can use
years, and months, or you only modify this formula as well you need...


Ismael Guzma
 
Back
Top