CODEWITHSUNDEEP
google-sheetstimedatetimerangefield
John Smith
John Smith

Reputation: 1

Googl Sheets - Time Range to Table

I want to take a time range and convert it to minutes within a table of 30 minute segments. For example, Bob works from 3:35 AM to 5:00 AM. So within the table from 3:30 AM - 4:00 AM should represent 25 minutes, because Bob was working a total of 25 minutes during that time slot. Then the time slots for 4:00 - 4:30 AM and 4:30 - 5:00 AM would both have 30 in their corresponding cells.

Apologies to leave this so broad but I honestly have no idea where to start and this forum has been immensely helpful.

https://docs.google.com/spreadsheets/d/1YpHU-UHlqXL6c8I27zSDZaRu72ViUw5W6RPru-HE3Iw/edit#gid=0

Any help is appreciated.

Upvotes: 0

Views: 99

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

For each 30-minute interval, you have to check whether these two conditions are met:

  • The interval start time (3:30) falls between the employee start and end time (3:35 and 5:00).
  • The interval end time (4:00) falls between the employee start and end time.

If any of these conditions are met, the working time for that interval won't be 0. The working time will be the difference between the minimum of both end times (4:00 and 5:00) and the maximum of both start times (3:30 and 3:35).

Translated to sheets functions, you could do something like this:

=IF(MIN($D2,O$1)-MAX($C2,N$1)>0,TIMEVALUE(MIN($D2,O$1)-MAX($C2,N$1))*24*60,0)

Or, alternatively, this:

=IF(OR(AND($C2<N$1,N$1<$D2),AND($C2<O$1,O$1<$D2)),TIMEVALUE(MIN($D2,O$1)-MAX($C2,N$1))*24*60,0)

Upvotes: 0

Related Questions