Forum Discussion

Hidden_User2304's avatar
Hidden_User2304
Copper Contributor
Jul 24, 2024

Timesheet calculations - help please

Going round in circles and finally admitting defeat. Photo of the data set I am currently working with - this relates to hours worked for staff. 

What I need to do in the unsocial hours column (Col O) is account for any hours worked between midnight (23:59) and 6am (06:00). E.g. for the first Row O2 should read 02:20 for 2hrs 20mins. 

First thing i'm stuck on is i have used =TEXT(H2, "HH:MM:SS") in columns M and N to convert the date/time in H and I into time only. However - this isn't overwriting the original date in the underlying data, so every combination of IF statements that I try is giving false results. 

 

 

I'm sure there is a very simple and straightforward answer, my brain is just fried. Thanks very much. 

 

 

excel
Formulas and Functions
  • djclements's avatar
    djclements
    Bronze Contributor
    Jul 24, 2024

    Hidden_User2304 A couple of shortcuts...

     

    To extract the date from a date/time value:

     

    =INT(date_time)
=INT(H2)

     

    To extract the time from a date/time value:

     

    =MOD(date_time,1)
=MOD(H2,1)

     

    Then, apply custom formatting to the results as desired (e.g. hh:mm:ss).

     

    Regarding your "Unsocial Hours" calculation, assuming a shift is never more than 18 hours long, you could try the following formula in cell O2 and copy it down:

     

    =MAX(MIN(MOD(I2,1),0.25)-MOD(H2,1)*(INT(H2)=INT(I2)),0)

     

    Then, apply custom formatting to display the results as [h]:mm.

     

    However, if a single shift could be more than 18 hours long, and potentially start before 6am on one day (e.g. 07/01/2024 5:00 AM) and end after midnight the next day (e.g. 07/02/2024 1:00 AM), the formula would need to be modified as follows:

     

    =MIN(MOD(I2,1),0.25)+MAX(0.25-MOD(H2,1),0)+(INT(I2)-INT(H2)-1)*0.25

     

    Also, if you used =MOD(H2,1) in cell M2 and =MOD(I2,1) in cell N2, you could simplify the formula with cell references:

     

    =MIN(N2,0.25)+MAX(0.25-M2,0)+(INT(I2)-INT(H2)-1)*0.25

     

    Please see the attached sample workbook, if needed, which also contains other examples...

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jul 24, 2024

    Hidden_User2304 

    I offer my DownTime Lambda repurposed to fit your needs.  This type of request appears every so often and I've used this function with 2 or 3 other requests.

     

    At the sheet level it looks like:

     

    The function (it's a bit lengthy because I wrote it so anyone can read it easier) :

     

     

    DownTime = LAMBDA(start, stop,
        LET(
            Min_in_day, 1440,
            one_minute, 1 / Min_in_day,
            TotalMinutes, LAMBDA(begin, end,
                LET(
                    duration, (end - begin) * Min_in_day,
                    TotalTime, SEQUENCE(
                        duration,
                        ,
                        begin,
                        one_minute
                    ),
                    hr, TotalTime - INT(TotalTime),
                    Midnight, 0 / 24,
                    SixAM, 6 / 24,
                    include, ((hr >= Midnight) * (hr <= SixAM)),
                    Actual, FILTER(TotalTime, include, 0),
                    SUM(SIGN(Actual)) / Min_in_day
                )
            ),
            IFERROR(
                MAP(start, stop, TotalMinutes),
                (stop - start) * Min_in_day
            )
        )
    )

     

     

     

    To get access to DownTime:

    1. Move your data into the attached workbook

    OR

    2. Move/Copy the sheet from the attached workbook into your actual workbook.

     

Resources