ExcelBanter - Calculating Time

Saved from a previous post:

You could use the 1904 base date
(in xl2003 menus)
Tools|Options|calculation tab|check "1904 date system"

Be aware that your dates will now be off by 4 years and one day.

And copying data (dates and times) between workbooks with different base dates,
will be a big problem.

One way to add (or subtract) those four years back is to find an empty cell, put
1462 into that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (or
subtract) (in theoperation box) and check values.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

================
If you're doing calculations and getting negative times that appear as ###'s,
you could use multiple cells -- one for the calculation (and further
calculations) and one to display the pretty time.

For instance, if you have times in A1 and B1, you could use C1 to subtract the
times:
=a1-b1
and D1 to make it look pretty:
=if(c1<0,"-","")&text(abs(c1,"hh:mm:ss"))

You could even hide column C if you find those ###'s irritating to see.

Fie M-C wrote:

Hi
I'm setting up a timesheet but when I enter the format to + or - if the
'time' is negative the figures are not showing - is there a way around this?

Thanks guys

--

Dave Peterson