+ Reply to Thread
Results 1 to 4 of 4

How do I convert start and end times to duration, days to number of days?

  1. #1
    Registered User
    Join Date
    11-08-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2016
    Posts
    4

    Question How do I convert start and end times to duration, days to number of days?

    Hello,
    I’m trying to calculate number of quarter hours in data that looks like this [brackets indicate separate cells].
    Days Start time End time
    [MTuWThF] [10:00PM] [2:00AM]

    So, in the example, the number of quarter hours is 5 days X 4 hours X 4 quarters = 80

    I have a wide variety of day combos – MTuW, MW, ThF, SaSu, MTWTh etc. It’s also important that I’m able to calculate # of hours for times that span from am to pm and pm to am.
    I think I can figure out counting the days to get that multiplier, but what about an easy way to count number of hours?

    And bonus would be handling ½ and ¼ hour times – 2:45, 3:15 etc.

    It’s a process I’ll have to apply to a couple thousand lines of data several times a year, so the more turnkey, the better. Any help is most appreciated, thanks!
    Tom

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: How do I convert start and end times to duration, days to number of days?

    Try:

    =COUNT(FIND({"M";"T";"W";"F";"S"},MID(A1,{1,2,3,4,5,6,7,8,9,10},1)))*(C1+(C1<B1)-B1)*24*4

    A1: Days
    B1: Start Time
    C1: End Time
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    10-10-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003, 2007 & 2010
    Posts
    88

    Re: How do I convert start and end times to duration, days to number of days?

    Hi constructionzone,

    If you're not restricted to having the Start and End Times in a single cell each, then perhaps the attached solution might help?

    121109 - Hours.xlsx

  4. #4
    Registered User
    Join Date
    11-08-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: How do I convert start and end times to duration, days to number of days?

    Thanks to you both, a modified version of the count formula is working pretty well. We have a weird situation where we're often converting times from clock times like 6:05 to a quarter hour 615p. If anyone knows of a way to have Excel understand the number of quarter hours with start and end times formatted like 2p, 615p, 3a, 11a etc. Let me know, otherwise, we're doing some things manually, but the formula above is saving a lot of time.

    Thanks!
    CZ

+ 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