| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to work out a timesheet with times going into next day (24 h
Stop using *fake* times (26:00)!
A1 = 18:00 B1 = 2:00 Formula in C1: =MOD(B1-A1,1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "z_insyd" wrote in message ... Im trying to work out a time sheet for work and the problem is that i work with 24 hr clock and shifts can start at any time over that period and finish around 8 hrs later... i need one result in the formula to answer the time worked in one day <within 24 hr's and another with the next day. This is where it gets difficult... when eg1 All in same day Start time = 09:00 (A1) Finish time = 17:00 (B1) Time worked = 8:00 (C1) ------------------------- Mon - Fri time = 8:00 (D1) Sat time = 0:00 (E1) eg 2. Working same amount of time but past midnight. Start time = 18:00 (A1) Finish Time = 26:00 (B1) Time worked = 8:00 (C1) ------------------------- Mon - Fri time = 6:00 (D1) Sat time = 2:00 (E1) The rough formula i can work out is that C1=B1-A1 D1=IF(B1<24:00,C1,24:00-A1) E1=IF(B1<24:00,0,(B1-24:00) My problem is because im dealing with a time past 24:00 the equations become invalid. The bit after that where in multiplying the totals by the rate is fine that works out as x=SUM(D1:D14)*24*100% and x=SUM(E1:E14)*24*150%. that works perfect. its just the start where all the trouble is. Can anyone help me? Z |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to work out a timesheet with times going into next day (
k that could solve a problem in C1 but it doesnt help with D1 or E1
"Ragdyer" wrote: Stop using *fake* times (26:00)! A1 = 18:00 B1 = 2:00 Formula in C1: =MOD(B1-A1,1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "z_insyd" wrote in message ... Im trying to work out a time sheet for work and the problem is that i work with 24 hr clock and shifts can start at any time over that period and finish around 8 hrs later... i need one result in the formula to answer the time worked in one day <within 24 hr's and another with the next day. This is where it gets difficult... when eg1 All in same day Start time = 09:00 (A1) Finish time = 17:00 (B1) Time worked = 8:00 (C1) ------------------------- Mon - Fri time = 8:00 (D1) Sat time = 0:00 (E1) eg 2. Working same amount of time but past midnight. Start time = 18:00 (A1) Finish Time = 26:00 (B1) Time worked = 8:00 (C1) ------------------------- Mon - Fri time = 6:00 (D1) Sat time = 2:00 (E1) The rough formula i can work out is that C1=B1-A1 D1=IF(B1<24:00,C1,24:00-A1) E1=IF(B1<24:00,0,(B1-24:00) My problem is because im dealing with a time past 24:00 the equations become invalid. The bit after that where in multiplying the totals by the rate is fine that works out as x=SUM(D1:D14)*24*100% and x=SUM(E1:E14)*24*150%. that works perfect. its just the start where all the trouble is. Can anyone help me? Z |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I setup plain work sheet for preparing resume or timesheet | Setting up and Configuration of Excel | |||
| Timesheet Calculate Sunday times that changes every month | Excel Discussion (Misc queries) | |||
| Chart that shows work times | Excel Discussion (Misc queries) | |||
| UDFs return #NAME error sometimes, other times, they work | Excel Discussion (Misc queries) | |||
| The left function does not work when displaying times, how is thi. | Excel Worksheet Functions | |||
Hybrid Mode
