| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Work hours calculation
Hi everyone,
I have a problem with calculating work hours. I use the normal simple formula Ex: =(B1-A1)*24 Assuming that 'A1' is the in time and 'B1' is the out time. This works OK, but if the in time is before midnight and the out time is after midnight it does not work. I managed to get the answer right by adding 24 to the formula but then it will not work properly for time before midnight. I appreciate your help and thank you. Albert - Malta |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Work hours calculation
Assuming A1,B1 are in hh:mm format:
=MOD(B1-A1,1) "albertmb" wrote: Hi everyone, I have a problem with calculating work hours. I use the normal simple formula Ex: =(B1-A1)*24 Assuming that 'A1' is the in time and 'B1' is the out time. This works OK, but if the in time is before midnight and the out time is after midnight it does not work. I managed to get the answer right by adding 24 to the formula but then it will not work properly for time before midnight. I appreciate your help and thank you. Albert - Malta |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Work hours calculation
Either use Toopers' suggestion and multiply by 24, i.e.
=MOD(B1-A1,1)*24 or =(B1-A1+(A1B1))*24 "albertmb" wrote: Hi everyone, I have a problem with calculating work hours. I use the normal simple formula Ex: =(B1-A1)*24 Assuming that 'A1' is the in time and 'B1' is the out time. This works OK, but if the in time is before midnight and the out time is after midnight it does not work. I managed to get the answer right by adding 24 to the formula but then it will not work properly for time before midnight. I appreciate your help and thank you. Albert - Malta |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Work hours calculation
Thank you Toppers and Daddylonglegs, formula worked perfectly.
All the Best Albert "Toppers" wrote: Assuming A1,B1 are in hh:mm format: =MOD(B1-A1,1) "albertmb" wrote: Hi everyone, I have a problem with calculating work hours. I use the normal simple formula Ex: =(B1-A1)*24 Assuming that 'A1' is the in time and 'B1' is the out time. This works OK, but if the in time is before midnight and the out time is after midnight it does not work. I managed to get the answer right by adding 24 to the formula but then it will not work properly for time before midnight. I appreciate your help and thank you. Albert - Malta |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hours calculation in 24h format | Excel Discussion (Misc queries) | |||
| Schedule hours calculation | Excel Discussion (Misc queries) | |||
| HOURS CALCULATION | Excel Discussion (Misc queries) | |||
| Removing non Work Hours from Excel calculation | Excel Worksheet Functions | |||
| Calculation of full hours | Excel Worksheet Functions | |||
Linear Mode
