Time schedule

[Zsolt Szabó]

Hello Everybody!

My problem is this: Column B / Format Cells / Custom / hh:mm
B5=17:00 B5 17:00
B6=21:48 B6 21:48
B7=($B$6-$B$5)+B6 B7 02:36
B8=($B$6-$B$5)+B7 B8 07:24
B9=($B$6-$B$5)+B8 B9 12:12

BUT as soon as I type in into B5 21:48 and B6 02:36 in B7 and the rest of
the cells below shows this ####### as a result. I know this is not a
challenging question for you guys but please help me with this.

 

[Dave Peterson]

Excel doesn't play nice with negative times unless you change a setting.

Tools|Options|Calculation tab|Check 1904 date system

Be aware that your existing dates will be now be off by 4 years and one day.
You can fix this, though. But a bigger problem may be when you copy dates from
one workbook to another (each using different date systems).

Saved from a previous post:

Be aware that any existing date will now be off by 4 years and 1 day. And
copying dates between workbooks becomes a problem, too.

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add 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 (in the
operation box).

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

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

(I'm not sure which one you'll fix. You may want to edit|pastespecial|click
subtract.)

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

 

[Harald Staff]

Do what Dave says. But also, I suspect the problem may be that the man ends
work (at 02:36) hours before he arrives (21:48). Standard solution for
calculating working hours is
=B6-B5+(B6<B5)

Best wishes Harald

 

[Dave Peterson]

Just to add to Harald's note...

If you're tracking times, you may find that it's safer to include the date and
time in your entry. Then you can subtract the earlier time/date from the later
time/date and not have to worry about how many hours or days passed.