+ Reply to Thread
Results 1 to 12 of 12

Calculating Time across a 24 hour period

  1. #1
    Registered User
    Join Date
    11-06-2007
    Posts
    10

    Calculating Time across a 24 hour period

    I'm struggling to find the appropriate formula to calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day.

    Example

    In B5 Startime is 22:00
    In C5 Finishtime is 06:30

    In I3 increment begins at 00:00
    In I4 increment ends at 00:30

    The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.

    Each time I feel I'm close I realize I'm not!!!

    Help

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    How about

    =IF(B5>C5,1,0)
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    11-06-2007
    Posts
    10
    Quote Originally Posted by oldchippy
    How about

    =IF(B5>C5,1,0)
    It's a bit more complex than this. I'm attempting to return a value if the specific shift time falls into a particulare hour of day increment.

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    If you want times in 30min intervals to represent numbers, then may be something like this will help? If you put 00:00 in A1 and 00:30 in A2 and auto-fill down, then in B1 put this

    =HOUR((A1*60)/30) format to General and auto-fill down

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    What if the time period only partially intersects the shift, e.g. time period 00:00 - 00:30, shift 18:00 - 00:15 should this give a 1 or a zero?

  6. #6
    Registered User
    Join Date
    11-06-2007
    Posts
    10
    Quote Originally Posted by daddylonglegs
    What if the time period only partially intersects the shift, e.g. time period 00:00 - 00:30, shift 18:00 - 00:15 should this give a 1 or a zero?
    The time can't intersect in the middle as these shifts are standard and run to the half hour.

    The problem I seem to be running into is related (I think) to the fact that some shifts begin on day one in my spreadsheet (08:30 - 17:00) and other shifts (22:00 - 06:30) span two days.

    I'm looking to return a 1 or a 0 in the various 1/2 time intervals that span a day from 00:00 - 23:59. As a result, my spread sheet contains the following 48 intervals, 00:00 - 00:30, 00:30 - 01:00, 01:00 - 01:30 and so on. The intervals are listed accross the spreadsheet. I have vavious shifts, some start at 22:00 and finish at 06:30 the next day. Some shifts start at 08:30 and finsh at 17:00 the same day. Each shift is listed down the spread sheet with the start time in one column and the finish time in the next column.

    I'm looking to populate the body of the spreadsheet with either a 1 or 0 based on the idea that the time interval being evaluated (lets say 16:00 - 16:30) is represented within a particular shift. In the example above, the 22:00 - 06:30 shift would be a 0 and the 08:30 - 17:00 shift would have a 1 because the 16:00 - 16:30 time interval exists in the 08:30 - 17:00 shift but does not exist in the 22:00 - 06:30 shift.

    What I need to be left with is a spreadsheet that has a bunch of 1's and 0's in various time intervals. I'll then be able to sum each time interval and the spreadsheet will tel me how many resources I have available at each interval.

    Thanks for the reply!

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    OK, so it's sufficient to check if the midpoint of the range is with the shift time. Use this formula copied across

    =(((I4+I3)/2>$B$5)+((I4+I3)/2<$C$5)+($B$5>$C$5)=2)+0

    Note: for your last ½ hr period use 23:30 - 24:00 [not 00:00]

  8. #8
    Registered User
    Join Date
    11-06-2007
    Posts
    10

    Smile

    Quote Originally Posted by daddylonglegs
    OK, so it's sufficient to check if the midpoint of the range is with the shift time. Use this formula copied across

    =(((I4+I3)/2>$B$5)+((I4+I3)/2<$C$5)+($B$5>$C$5)=2)+0

    Note: for your last ½ hr period use 23:30 - 24:00 [not 00:00]
    Thank you. This is very close. I'm having problems changing the format of my last hour from 00:00 to 24:00.

    I format the cell to hh:mm but it continues to convert to 00:00?

    Thanks

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    For the purposes of my suggested formula, as long as you enter 24:00 (or 1, because a day = 1 in excel) then it won't matter if the cell shows as 00:00....but if you want it to display as 24:00 custom format the cell as [h]:mm.

    You say "this is very close". Was there anything else that didn't work as you wanted?

  10. #10
    Registered User
    Join Date
    11-06-2007
    Posts
    10
    Quote Originally Posted by daddylonglegs
    For the purposes of my suggested formula, as long as you enter 24:00 (or 1, because a day = 1 in excel) then it won't matter if the cell shows as 00:00....but if you want it to display as 24:00 custom format the cell as [h]:mm.

    You say "this is very close". Was there anything else that didn't work as you wanted?

    My shifts have the possibility of begining on one day and and ending on the next day in the example of 22:00 - 06:30. My intervals begin at 00:00 and end at 24:00. I've been forced to extent my spreadsheet of intervals over two days (00:00 - 09:00 the next day) because the spreadsheet will not recognize that the 22:00 - 06:30 shift exists in the first interval 00:00 - 00:30 listed at the begi:ning of the spreadsheet. The problem I run into really happens once the clock turns. I need to represent the interval 05:30 - 06:30, as an example, twice to pickup the shift that begins and ends 22:00 - 0630 and 06:30 - 15:00. For purposes of my spreadsheet, the resources that work those shifts are there at the same time.

    Thanks again!

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    The formula I posted should work as you want. It recognises that a shift from 22:00 to 06:30 covers all ½ hour periods from 22:00-22:30 to 06:00-06:30 inclusive, see attached
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-06-2007
    Posts
    10
    Thank you.

    I have the formula working!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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