+ Reply to Thread
Results 1 to 4 of 4

Calculate hours falling inside a time interval

  1. #1
    Registered User
    Join Date
    01-07-2021
    Location
    Basingstoke, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    1

    Question Calculate hours falling inside a time interval

    I am trying to find the correct excel formula to use: I have 2 cells that contain a start time [cell E2] and an end time [cell F2]. I want to calculate how many hours there are between the timeframe of 00:00 and 08:00 based on those start and end times. I have found a formula that works:

    =IF((E2*24)<8,8-(E2*24),IF((F2*24)<8,F2*24,""))

    However the formula will be exported into excel using this identical formula across every row, therefore I am unable to specify rows only columns to calculate each row of the worksheet correctly.
    I then found one formula that works to calculate the hours after 00:00 only, referencing only the end time time in column F:

    =SUM(INDIRECT(ADDRESS(ROW(),6))*24)

    and one formula to calculate hours before 08:00, referencing only the start time in column E:

    =SUM(8-(INDIRECT(ADDRESS(ROW(),5))*24))

    I would like to find a way to combine the last two formulas to achieve the same result from the first formula I used when I specified the cells to be used. Can anyone help? Any suggestions would be appreciated in achieving this or any other alternative solution.
    Attached Files Attached Files
    Last edited by Davidi81; 01-08-2021 at 04:57 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,098

    Re: Calculate hours falling inside a time interval

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. If I understood what you are trying to do, I'd have done it for you ...)

    EDIT: Glenn did it for you.
    Last edited by AliGW; 01-07-2021 at 12:54 PM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Calculate hours falling inside a time interval

    Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

    A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

    Since you are a new user of this site, on this occasion, I have changed it for you.

    However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

    *Other search engines are available!!!

    ALSO...



    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Glenn



  4. #4
    Registered User
    Join Date
    09-14-2020
    Location
    GA, USA
    MS-Off Ver
    EXCEL 365; 2019
    Posts
    40

    Re: Calculate hours falling inside a time interval

    Disregard.
    Last edited by Nvable; 01-11-2021 at 10:27 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate Shift Hours (Date/Time difference in an interval)
    By seanpark22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2020, 04:39 PM
  2. [SOLVED] How to calculate time remaining in an interval?
    By prudential in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2018, 11:04 AM
  3. [SOLVED] How to calculate time interval
    By VAer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-02-2018, 02:19 PM
  4. [SOLVED] [SOLVED] Calculate time worked inside Regular Hours and Over Time
    By myxamhatosis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-07-2015, 09:16 PM
  5. Expressing time elapsed as falling between a specific interval
    By BLat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-17-2015, 05:51 PM
  6. Formula to count number of patients per time interval over 24 hours+
    By RM1984 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2014, 11:30 AM
  7. [SOLVED] Formula to Calculate Normal Hours, Time & 1/2 & Double Time from Daily Hours per week
    By KazzICC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2014, 01:51 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1