# Google Sheets - duration between two specific times

I'm having a little trouble writing a formula to measure duration between two specific times within a time range.

i.e. Time range: 1300-2330

Total hours=10:30

Hours between 2200-0000=1:30

So far I've only managed to measure total duration between two times adjusting for past midnight with Total hours=1700-1300+(1700<1300)

I've trawled the net and attempted some IF functions to no avail. Below is a quick example.

Any help is much appreciated!

• Try =IF(B2<A2,B2-A2+1,B2-A2). So if end time is less than start we adjust it for 24 more hours adding "+1" as time number from 0 to 1. Commented Mar 17, 2021 at 9:41
• Thanks for your help mate. Interesting to see another way to achieve the past midnight conundrum. I've managed to cover the total duration between two times though. It's the duration between two specific times within a time range that I'm struggling with. So if the range was 1600-2330, how many hours between 2200-0000. (1.5) Commented Mar 18, 2021 at 15:28
• Hours between 1530-1800=2.5 not 1.5 Commented Mar 20, 2021 at 2:58
• Thanks @Tedinoz for the correction in the example. Simply a typo. I've rewritten it to make it clearer. As for the question, I've been trying to measure the duration between two times within a specified range. Wages for instance. I can calculate total hours with Oleg_S' formula or my formula, however I also need to calculate an additional rate between certain times i.e. 2200-0000 there is a higher wage to calculate. So within the total hours between 1300-2330 (10:30hrs) there's also an additional rate between 2200-0000 (1:30hrs). I'm struggling to automate the counting of the 1:30hrs duration. Commented Mar 23, 2021 at 3:25

A person works within a given time range (let's call this the "SHIFT"). You want to calculate whether any (and how many) SHIFT hours fall within a second time range (let's call this the "ALLOWANCE PERIOD").

Consider this logic:

1. Is the end time for the SHIFT greater than the start time for ALLOWANCE PERIOD?
• if yes, the person has worked some hours within the ALLOWANCE PERIOD.
• if no, the person hasn't worked any hours within the ALLOWANCE PERIOD.

Assuming that the person has worked some hours within the ALLOWANCE PERIOD, consider:

1. Is the end time for the SHIFT greater than the end time for the ALLOWANCE PERIOD?
• if yes, then the ALLOWANCE hours equals all the hours in the ALLOWANCE PERIOD.
• if no, then the ALLOWANCE hours equals the hour difference between the end of the SHIFT and the start of the ALLOWANCE PERIOD.

This is the formula, based on the sample data shown below.

`=if(B4>B8,if(B4>=(B9+1),mround((B9+1)-B8, time(0, 15, 0))*24,mround((B4)-B8, time(0, 15, 0))*24),0)`

SAMPLE DATA

• Thanks so much! You're an absolute champion. Commented Mar 26, 2021 at 14:53