Subtracting time

P

Pantryman

Hi all,

who knows how to do this?

I want to subtract time, but Excel won't take 00:00 - 10:00.
Cell format is [hh]:mm.

Thanks in advance.
 
H

Harald Staff

Hi

Excel takes it pretty well, but can't display negative time unless you
choose 1904 calendar standard in the Tools > Options menu.

Note that this action will push existing dates 4 years off. Post back if
that is a problem.

HTH. best wishes Harald
 
J

Johannes

00.00 is in fact 0 (the number 0) or in time 00 january 1900 0:00 AM

10.00 is in fact 0.416667 ( or 10/24 of 00 january 1900 10.00 AM) The
problem is that 00:00 - 10:00 is in fact negative, which excel cannot handle

If cell A1 contains 00:00 and A2 contains 10.00 you could use in cell A3
something like
= IF(A2 > A1; A1 + 1 - A2; A1 - A2)

By the way: be sure if you only use time (like in hours) that you not by
mistake add 1 (which in fact is 1 day or 24 hrs and not 1 hr!!).

JP
 
P

Pantryman

Hi,

since this spreadsheet will be used by several users the 1904 solutio
won't be practical although it indeed works great.

The time calculations are not date-dependent so perhaps there is
better solution then adding or subtracting a day?
I could for example subtract an additional 4:00 hours, but will tha
always work?

=IF(A2 > A1,A1 + 1 - A2 - 4/24,A1 - A2)

doesn't work since it will return 11:00 if A1=1:00 and A2=10:00.

The result should be the difference between A1 and A2;

A1=10:00
A2=0:00
A3=10:00

A1=0:00
A2=0:00
A3=0:00

A1=5:00
A2=6:00
A3=-1:00

A1=6:00
A2=5:00
A3=1:00

thanks so far
 
H

Harald Staff

Pantryman said:
Hi,

since this spreadsheet will be used by several users the 1904 solution
won't be practical although it indeed works great.
Click to expand...

Wrong, my friend. 1904 calendar is a workbook property, not an Excel
setting. Using a 1904 file is not a problem to anyone. Conflicts appear only
when dates are copy-pasted to/from the file, and when remote workbooks link
to it.

HTH. best wishes Harald
 
P

Pantryman

Hi Harald,

allright. :)

The next development for my sheet is the import/export of a txt fil
containing dates.
If this file is imported into a '1904' worksheet from a 'standard
worksheet, what will happen?
Also, if a user copy-pastes dates from a 'standard' worksheet into hi
'1904' worksheet (or vice-versa) will that be a problem?

If not, I'll use this solution.

Marinus
 
H

Harald Staff

Hi Marinus

Import should work fine. Copy-paste should not work fine. So how much of a
problem this is depends on its use and how important dates are.

This thing has to do with Windows - Macintosh standards. Windows use 1900
calendar by default, Mac 1904. Excel can override a workbook to use to the
other standard on both platforms. In a mixed environment this is and has to
be a pain. Dates will shift 1462 days -but once you really think you know
which direction, it's the other one. But 1462 it is, so once you (and/or
macros) know what happens where then it's easy to fix.

But as initially stated; Excel has no problems with negative time, just
displaying it. If it's for calculations only then it's not a problem except
visually.

HTH. Best wishes Harald
 
Top