| 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
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 (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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to work out a timesheet with times going into next day (24 h
Try these formulas
in D1 =MAX(0,B1-1) and in E1 =C1-D1 "z_insyd" wrote: 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 |
|
#4
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to work out a timesheet with times going into next day (
I managed to get E1 working using E1=IF((C1-B1)<0,0,B1)
(still not sure how i tweaked it, but it works that what matters) That was also substituting C1=MOD(B1-A1,1) that Ragdyer gave to slove problems with having to use numbers 24 The only problem is trying to get an answer to D1 i'll keep playing with it unless someone comes up with anything. Thanks! "daddylonglegs" wrote: Try these formulas in D1 =MAX(0,B1-1) and in E1 =C1-D1 "z_insyd" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to work out a timesheet with times going into next day (
I GOT IT!!!
D1=IF(B1<24,MOD(B1-A1,1),0) E1=IF((C1-B1)<0,0,B1) Thanks again for ur help!! "z_insyd" wrote: I managed to get E1 working using E1=IF((C1-B1)<0,0,B1) (still not sure how i tweaked it, but it works that what matters) That was also substituting C1=MOD(B1-A1,1) that Ragdyer gave to slove problems with having to use numbers 24 The only problem is trying to get an answer to D1 i'll keep playing with it unless someone comes up with anything. Thanks! "daddylonglegs" wrote: Try these formulas in D1 =MAX(0,B1-1) and in E1 =C1-D1 "z_insyd" wrote: 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 | |||
Linear Mode
