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.


--
Pantryman
------------------------------------------------------------------------
Pantryman's Profile: http://www.excelforum.com/member.php...o&userid=15233
View this thread: http://www.excelforum.com/showthread...hreadid=320065

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

"Pantryman" skrev i melding
...

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.


--
Pantryman
------------------------------------------------------------------------
Pantryman's Profile:
http://www.excelforum.com/member.php...o&userid=15233
View this thread: http://www.excelforum.com/showthread...hreadid=320065

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


"Pantryman" schreef in bericht
...

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.


--
Pantryman
------------------------------------------------------------------------
Pantryman's Profile:
http://www.excelforum.com/member.php...o&userid=15233
View this thread: http://www.excelforum.com/showthread...hreadid=320065

Hi,

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

The time calculations are not date-dependent so perhaps there is a
better solution then adding or subtracting a day?
I could for example subtract an additional 4:00 hours, but will that
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!


--
Pantryman
------------------------------------------------------------------------
Pantryman's Profile: http://www.excelforum.com/member.php...o&userid=15233
View this thread: http://www.excelforum.com/showthread...hreadid=320065

"Pantryman" skrev i melding
...

Hi,

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

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

Hi Harald,

allright. :)

The next development for my sheet is the import/export of a txt file
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 his
'1904' worksheet (or vice-versa) will that be a problem?

If not, I'll use this solution.

Marinus.


--
Pantryman
------------------------------------------------------------------------
Pantryman's Profile: http://www.excelforum.com/member.php...o&userid=15233
View this thread: http://www.excelforum.com/showthread...hreadid=320065

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

"Pantryman" skrev i melding
...

Hi Harald,

allright. :)

The next development for my sheet is the import/export of a txt file
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 his
'1904' worksheet (or vice-versa) will that be a problem?

If not, I'll use this solution.

Marinus.


--
Pantryman
------------------------------------------------------------------------
Pantryman's Profile:
http://www.excelforum.com/member.php...o&userid=15233
View this thread: http://www.excelforum.com/showthread...hreadid=320065