Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a timesheet!!!
How can I, just by inserting work period eg 8 16, afterwards in 2 cells see
all hours between 18 and 22 and hours between 22 and 5:15? Eg as written he working time evening hours night hours Patricia 12 20 2 0 Maria 16 24 4 2 -Where I only have to write the working time and excel calculates the rest... I'm really having difficulties. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a timesheet!!!
Pat wrote:
How can I, just by inserting work period eg 8 16, afterwards in 2 cells see all hours between 18 and 22 and hours between 22 and 5:15? Eg as written he working time evening hours night hours Patricia 12 20 2 0 Maria 16 24 4 2 -Where I only have to write the working time and excel calculates the rest... I'm really having difficulties. Thanks! if 12=12:00 and 24=midnight, how do you plan on entering minutes? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a timesheet!!!
Pat wrote:
How can I, just by inserting work period eg 8 16, afterwards in 2 cells see all hours between 18 and 22 and hours between 22 and 5:15? Eg as written he working time evening hours night hours Patricia 12 20 2 0 Maria 16 24 4 2 -Where I only have to write the working time and excel calculates the rest... I'm really having difficulties. Thanks! Assuming that you enter the times as 12:00 20:00 16:00 00:00 Columns A & B, then these formulas should hopefully do what you want: For evening hours: =IF((MIN(B2,TIME(22,0,0))-MAX(TIME(18,0,0),A2))*240,(MIN(B2,TIME(22,0,0))-MAX(TIME(18,0,0),A2))*24,0) For night hours: =IF((MIN(IF(B2<=TIME(5,15,0),B2+1,B2),1+TIME(5,15, 0))-MAX(TIME(22,0,0),A2))*240,(MIN(IF(B2<=TIME(5,15,0 ),B2+1,B2),1+TIME(5,15,0))-MAX(TIME(22,0,0),A2))*24,0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timesheet Help. Rounding to the nearest 1/4 | Excel Worksheet Functions | |||
displaying selected items from a yearplanner in a timesheet | Excel Discussion (Misc queries) | |||
making a timesheet | Excel Worksheet Functions | |||
overtime on timesheet | Excel Worksheet Functions | |||
timesheet with running total of overtime | Excel Worksheet Functions |