CODEWITHSUNDEEP
excelvbatime
letsdothis
letsdothis

Reputation: 233

How to handle dates across midnight in Excel

I have data with the following structure:

Date Time
01-01-2021 0800-1600
01-01-2021 2000-0400

Each line is an employee and their worked hours. Meaning the first line employee #1 meets 0800 and leaves at 1600 the same day. However employee #2 meets 2000 and leaves 0400 the following day.

My issue is that I'm working showing no. of employees present at specific times. The first employee is easy to do as meeting and leaving is the same day. However the second is a bit more problematic, as in my current setup the employee is shown on work the same day.

The data is updated automatically into excel so I don't want to make any manual adjustments. In order to show it correctly, and from my point of view, I need to make an additional line with the hours for the following day. I could make this with VBA, but I'm not sure if this is the easiest and best way to do it.

So any ideas on how to handle an issue like this?

Thanks!

Upvotes: 0

Views: 183

Answers (2)

CDP1802
CDP1802

Reputation: 16189

Create a User Defined Function (UDF) to return a datetime in cols C and D from the values in col A and B. Put the UDF code in a module. For example the formula in C2 would be =date_time($A2,$B2,0) and in D2 =date_time($A2,$B2,1)

Example

Option Explicit

' i=0 for start , i=1 for end
Function date_time(dt As Date, hrs As String, i As Integer) As Date
    Dim ar
    ar = Split(hrs, "-")
    If ar(1) < ar(0) Then ar(1) = ar(1) + 2400 ' next day
    ' add minutes
    date_time = DateAdd("n", Left(ar(i), 2) * 60 + Right(ar(i), 2), dt)
End Function

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

This formula will give your the hours worked:

=LET(DateValue, A2,
           TimeValues,FILTERXML("<t><c>"&SUBSTITUTE(B2,"-","</c><c>")&"</c></t>","//c"),
           Start,INDEX(TimeValues,1),
           End,INDEX(TimeValues,2),
           StartTime, SUM(DateValue, Start/2400),
           EndTime, IF(Start<End,SUM(DateValue,End/2400),SUM(DateValue+1,End/2400)),
           EndTime - StartTime)

This will give you the hours worked in day one or day two (change the last variable to Day1Hours):

=LET(DateValue, A2,
           TimeValues,FILTERXML("<t><c>"&SUBSTITUTE(B2,"-","</c><c>")&"</c></t>","//c"),
           Start,INDEX(TimeValues,1),
           End,INDEX(TimeValues,2),
           StartTime, SUM(DateValue, Start/2400),
           EndTime, IF(Start<End,SUM(DateValue,End/2400),SUM(DateValue+1,End/2400)),
           Day1Hours, IF(Start<End,EndTime-StartTime,(DateValue+1)-StartTime),
           Day2Hours,IF(End<Start, EndTime-(DateValue+1),0),
           Day2Hours)

This I was hoping would return both results, but FILTERXML didn't seem to work within the LET function when returning the results:

=LET(DateValue, A2,
           TimeValues,FILTERXML("<t><c>"&SUBSTITUTE(B2,"-","</c><c>")&"</c></t>","//c"),
           Start,INDEX(TimeValues,1),
           End,INDEX(TimeValues,2),
           StartTime, SUM(DateValue, Start/2400),
           EndTime, IF(Start<End,SUM(DateValue,End/2400),SUM(DateValue+1,End/2400)),
           Day1Hours, IF(Start<End,EndTime-StartTime,(DateValue+1)-StartTime),
           Day2Hours,IF(End<Start, DateValue+1-EndTime,0),
           TRANSPOSE(FILTERXML("<t><c>" & Day1Hours & "</c><c>" & Day2Hours & "</c></t>","//c")))

Upvotes: 1

Related Questions