0

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.

Example spreadsheet: https://docs.google.com/spreadsheets/d/1QY4o7RMkWGcZPNUZg5OAETtmmlm07E6yyFjFnu0Jgac/edit?usp=sharing

Any help is much appreciated!

Improve this question
5
  • 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.
    – Oleg_S
    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)
    – Daniel Ashby
    Commented Mar 18, 2021 at 15:28
  • Hours between 1530-1800=2.5 not 1.5
    – Tedinoz
    Commented Mar 20, 2021 at 2:58
  • 1
    Would you please clarify "what is your question"? The answer by @Oleg_S appears to resolve the question as asked. Your response about "a range of 1600-2330, how many hours between 2200-0000. (1.5)" doesn't correspond to the data in your spreadsheet or to the question as asked. You ask How many hours between 2200-0000 (1.5) Would you please explain how you calculate this duration as 1.5 hours, and also explain the relevance of this duration to a time range of "1600-2330". Would you also include this scenario (and the expected result) in your spreadsheet?
    – Tedinoz
    Commented Mar 20, 2021 at 3:29
  • 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.
    – Daniel Ashby
    Commented Mar 23, 2021 at 3:25

1 Answer 1

Reset to default
0

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

Snapshot

Improve this answer
1
  • Thanks so much! You're an absolute champion.
    – Daniel Ashby
    Commented Mar 26, 2021 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.