exceldatetimeexcel-formuladate-range

excel No of hours in a shift

We have two timestamps of when colleagues are expected on shift so an In an Out this is formatted as DD/MM/YYYY HH:MM, currently use the following code to create a lookup value, then lookup that value for hours expected on each shift in a day, while it works im not sure is the most efficient way.

=IFERROR(IF(A2<>"",IF(FIXED((NUMBERVALUE((MOD(DaysMerged!E2,1)))*24)*100,0,TRUE)="0",2400,FIXED((NUMBERVALUE((MOD(DaysMerged!E2,1)))*24)*100,0,TRUE))&IF(FIXED((NUMBERVALUE((MOD(DaysMerged!F2,1)))*24)*100,0,TRUE)="0",2400,FIXED((NUMBERVALUE((MOD(DaysMerged!F2,1)))*24)*100,0,TRUE)),0),0)

So this takes a date so for instance In 30/09/2023 14:00 & Out 30/09/2023 22:00 become 14002200, i then lookup this in a lookup table, which become

Night Before Earlies Lates Nights Day After
0 0 8 0 0

another example would be 30/09/2023 05:00 & Out 30/09/2023 13:00 so 5001300

Night Before Earlies Lates Nights Day After
1 7 0 0 0

What im after is a way to convert the In & Out timestamp into shift hours across 3 shifts, each shift is earlies 06:00-14:00 lates 14:00-22:00 then nights 22:00-06:00. once this is split i then need to get the hours for each shift to show how many hours the colleague is on shift for. so earlies, lates and nights are easy just get the value for them, then i have to add any overlap shift either before or after. Again i have this working, just looking for advise and efficient ways of going about this. any examples, sites etc are welcome.

Thanks.

Solution

  • Let's try this

    The idea is to renumber hours for a sequential value. From yesterday 22 to tomorrow 14

        C2                      D2
=HOUR(A2)+3   =24*(INT(VALUE(B2))-INT(VALUE(A2)))+HOUR(B2)+3  'edited by comment

    Formulas in columns from E to I

    E2:   =IF(C2<2;8;IF(C2<9;9-C2))-IF(D2<2;8;IF(D2<9;9-D2))
F2:   =IF(C2<10;8;IF(C2<17;17-C2))-IF(D2<10;8;IF(D2<17;17-D2))
G2:   =IF(C2<18;8;IF(C2<25;25-C2))-IF(D2<18;8;IF(D2<25;25-D2))
H2:   =IF(C2<26;8;IF(C2<33;33-C2))-IF(D2<26;8;IF(D2<33;33-D2))
I2:   =IF(C2<34;8;IF(C2<41;41-C2))-IF(D2<34;8;IF(D2<41;41-D2))

    Result:

    enter image description here

    This version calculates with minutes in the time.

        C2                      D2
=A2-INT(A2)+3/24   =1*(INT(VALUE(B2))-INT(VALUE(A2)))+B2-INT(B2)+3/24

    Formulas in columns from E to I

    E2:   =IF(C2<1/24;8/24;IF(C2<9/24;9/24-C2))-IF(D2<1/24;8/24;IF(D2<9/24;9/24-D2))
F2:   =IF(C2<9/24;8/24;IF(C2<17/24;17/24-C2))-IF(D2<9/24;8/24;IF(D2<17/24;17/24-D2))
G2:   =IF(C2<17/24;8/24;IF(C2<25/24;25/24-C2))-IF(D2<17/24;8/24;IF(D2<25/24;25/24-D2))
H2:   =IF(C2<25/24;8/24;IF(C2<33/24;33/24-C2))-IF(D2<25/24;8/24;IF(D2<33/24;33/24-D2))
I2:   =IF(C2<33/24;8/24;IF(C2<41/24;41/24-C2))-IF(D2<33/24;8/24;IF(D2<41/24;41/24-D2))

    Result: enter image description here