Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time schedule
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. -- Many thanks for your help in advance. Have a wonderful day! Zsolt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time schedule
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. Zsolt Szabó wrote: 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. -- Many thanks for your help in advance. Have a wonderful day! Zsolt -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time schedule
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 "Zsolt Szabó" wrote in message ... 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. -- Many thanks for your help in advance. Have a wonderful day! Zsolt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time schedule
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. Harald Staff wrote: 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 "Zsolt Szabó" wrote in message ... 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. -- Many thanks for your help in advance. Have a wonderful day! Zsolt -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill time schedule with gaps | New Users to Excel | |||
is there a way to schedule a macro to execute at a certain time? | Excel Discussion (Misc queries) | |||
Can I schedule a macro to run automatically at a certain time? | Excel Worksheet Functions | |||
template for a 24-7 time slot schedule | Excel Discussion (Misc queries) | |||
How to use solver to schedule part time and full time workforce? | Excel Discussion (Misc queries) |