How to pull info to get the proper sum

Hi,

Thanks for sharing the details. For older Excel, here’s a simple way to calculate Night Differential (18:00–06:00) and Sunday hours.

Add two helper cells:

Start: =B2+E2

End: =B2+F2+(F2<E2) (adds a day if OUT < IN)

Night Diff:

=(MAX(0, MIN(T2, INT(S2)+1) - MAX(S2, INT(S2)+TIME(18,0,0))) +

MAX(0, MIN(T2, INT(S2)+1+TIME(6,0,0)) - MAX(S2, INT(S2)+1)))*24

Sunday Diff:

First find Sunday: =INT(S2)-WEEKDAY(S2,2)+7

Then: =MAX(0, MIN(T2, V2+1) - MAX(S2, V2))*24

Format as numbers for hours. These formulas handle shifts crossing midnight and work in older Excel.

Hope this helps!

Edward

Hi,

Thank you for getting back , When I said “older Excel,” I was referring to versions before Office 365 or Office 2021/2024, such as Excel 2010, 2013, or 2016.

Those older versions don’t support some of the newer features like:

Dynamic arrays (FILTER, SORT, UNIQUE)

Modern functions (TEXTJOIN, IFS, LET, LAMBDA)

Better handling of time/date calculations, Since you’re on Office 365 and Office 2024, you have all the latest features, so the formulas I shared will work perfectly for you.

I hope this helps clear things up. If you need more clarification, feel free to post back.

Regards,

Edward

In order to compute Sunday hours, you need to know if the values in a row are for a Sunday. The easiest way would be to propagate each date value down to the next five rows which could contain data for that date. You don't have to display the value in these rows (change the color to white) but the values need to be there.

If you don't want to do that, the following formula will calculate the row that contains the date for a row that contains your in and out times:

INT((row_with_data-3)/6)*6+2

Once you have the row number for the date, you can use the INDEX or OFFSET function to access the date value and the WEEKDAY function to determine if it is a Sunday.

If the time worked spans midnight and starts on Saturday or Sunday, you will need to separate the Sunday part from the not-Sunday part.

Similarly, if the work spans 1800 or 0600, you will need to separate the day from the night.

Most time data like this does not include a date. So when the end time is "lower" than the start time, you will need to add 24 hours to the end time before calculating duration.