7

I'm trying to develop an Excel formula that would calculate the actual time duration for a string of a time range of the following format: 1:00am – 3:00am

According to this article it seems it should be as simple as subtracting the two times. For example, to get the hours, minutes, and seconds between two times (4:55:00), the article says to do the following:

=TEXT(B2-A2,"h:mm:ss")

Here's about how far I got:

=TEXT(RIGHT(B2,SEARCH(" – ",B2))-LEFT(B2,SEARCH(" – ",B2)),"h:mm:ss")

I wonder if the problem might be that my string of text has no "date" component in addition to the time. Is the only solution to being able to do the subtraction operation to "fudge" a date into the strings? Or is there another alternate approach, other than simply breaking down the hour and minute components individually and calculating the duration that way? I'd also like to compensate for any potential pm-to-am spans of time, such as 9:00pm — 12:00am if at all possible.

Improve this question
1
  • Ideally I don't want to use more than the two cells being mentioned... the cell with the timeframe, and the cell with the formula to calculate the duration of that time frame. So, the shortest single formula wins! :P
    – purefusion
    Aug 9, 2011 at 17:51

5 Answers 5

Reset to default
5

Here's one of the complicated solutions. It's probably best just to paste this in and hope for the best rather than trying to sit down and parse this.

=INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)) & ":" & TEXT((MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)-INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)))*60,"00")

Edit: Fixed the formula for the last time.

Addressing comment:

To add these output, to add a range of these output you can use the following formula:

=TEXT(SUM(VALUE(B2:B3)),"h:mm:ss")

You must enter this as an array formula. Do so by pressing Ctrl+Shift+Enter when entering the formula.

Improve this answer
14
  • That's definitely complicated! I could see a solution to overcome the am/pm space obstacle in a formula that would probably be shorter, but I digress. Anyway, I tried it, and it worked, but it's rather buggy, and obviously it's too complex for me to figure out why! ;)
    – purefusion
    Aug 9, 2011 at 17:33
  • Example of odd bugs: 1:00am – 10:30am calculates to -1:30, 10:30am – 11:30am calculates to -9:00, 12:30pm – 2:00pm calculates to -11:30, and 7:00pm – 11:30pm calculates to -6:30. I didn't double check if all the positive results were valid though.
    – purefusion
    Aug 9, 2011 at 17:33
  • Agh, you're right! Into the trenches I go... be back soon... I hope.
    – Excellll
    Aug 9, 2011 at 17:37
  • Okay, everything should be in working order now.
    – Excellll
    Aug 9, 2011 at 17:55
  • Heh, the update is even longer! Still more bugs though: 2:30am – 3:00am calculates to 1:00, 4:00am – 7:00am calculates to 1:00, 9:00pm – 3:00am calculates to 1:00 in one instance and 0:30 in another. What's up with that?! :P
    – purefusion
    Aug 9, 2011 at 18:01
2

If you can put a space before the "am" or "pm" then this will work:

=TEXT(RIGHT(B2,LEN(B2)-SEARCH(" - ",B2)-2) -
 LEFT(B2,SEARCH(" - ",B2)-1),"h:mm:ss")

Here's a basic one that will handle no space before the am/pm, but it's not robust or complete, since it won't handle a lot of the edge cases, just wanted to put it out to show some different ways of doing things. We'd need to know all the possible types of strings you'll be parsing to come up with a truly complete solution:

=TEXT((MID(B2,SEARCH(" - ",B2)+3, LEN(B2)-SEARCH(" - ",B2)-4) &  
       IF(ISERROR(SEARCH("am",RIGHT(B2,8))), " pm", " am")) - 
      (LEFT(B2,SEARCH(" - ", B2)-3) & 
       IF(ISERROR(SEARCH("am",LEFT(B2,8)))," pm"," am")),
      "h:mm:ss")
Improve this answer
6
  • Right, I just noticed that there was a space before the am/pm. Seems like that might be a bit complicated to pull off. Your formula looks good though! Compensates for the extra spaces on either end that my attempt had been causing.
    – purefusion
    Aug 9, 2011 at 17:01
  • The total solution from your approach probably can't be any longer or more complicated than the formula below! :)
    – purefusion
    Aug 9, 2011 at 17:57
  • @purefusion, ha, no one said this was a shortest formula contest! But yeah, if you can fit the requisite SUBSTITUTE functions into the formula, then that would probably be shorter. I do know that Excel limits the number of instances of SUBSTITUTE in a formula though, so that approach could fall flat because of that.
    – Excellll
    Aug 9, 2011 at 18:03
  • I checked. You can nest the SUBSTITUTE functions into this formula (2 around the RIGHT term, 2 around the LEFT term -- one for "am" and one for "pm" on each). And that will give you the seconds, too.
    – Excellll
    Aug 9, 2011 at 18:12
  • Ah, nice! Yeah, I decided to shake things up at the last minute... but sometimes I do wish I could mark more than one answer as correct!! I think in general I would take an understandable solution over a "black magic" solution, but I give you kudos for showing off, Excellll! :P
    – purefusion
    Aug 9, 2011 at 18:12
1

You need the space before "am" or "pm", as Lance Roberts said.

You can use this formula:

=SUBSTITUTE(SUBSTITUTE(B2; "am"; " am"); "pm"; " pm")

Then, try this:

=TEXT(TIMEVALUE(RIGHT(B2;SEARCH(" - ";B2;1)))-TIMEVALUE(LEFT(B2;SEARCH(" - ";B2;1)));"h:mm:ss")
Improve this answer
2
  • Ah, you mean make a second "interim" column to handle the am/pm space insertion, then operate based on those interim cells? Well that certainly might be cleaner than having to run the SUBSTITUTE function for every instance of B2 in one giant, complicated, single formula. ;)
    – purefusion
    Aug 9, 2011 at 17:30
  • Admittedly though, there are some instances where the interim column just may not be feasible (although that column could simply be hidden). shrug
    – purefusion
    Aug 9, 2011 at 17:37
1

Try this formula:

=A2+(A1>A2)-A1

where

A1 contains your "From" time
A2 contains your "To" time

You can then either use the TEXT function to format it into a string,
or use the number format [h]:mm:ss.

It should work even with time spans that range past midnight (i.e. 9:34PM to 3:45AM)

First, as Lance has mentioned, you need to add a space between the time and the AM/PM notations in your time string.

The easiest way to do this is through Excel's Find and Replace feature. Press Ctrl + H, type "am" into Find what and then type " am" (with a single space before it) into Replace with. Do the same with "pm."

Edit: [removed nested SUBSTITUTES] Nesting SUBSTITUTES would be a bad idea after all. At least not without adding TRIM.

Here's my other overkill approach.

Create a name ("tt") using formula below (where $A1:$A10 is the range that contains your time strings.

=TRIM(SUBSTITUTE(SUBSTITUTE($A1:$A10,"a"," a"),"p"," p"))

Then use this formula to get the time duration (based on the first formula above):

=RIGHT(tt,LEN(tt)-FIND("-",tt,1)-1)+
  (LEFT(tt,FIND("-",tt,1)-2)>RIGHT(tt,LEN(tt)-FIND("-",tt,1)-1))-
  LEFT(tt,FIND("-",tt,1)-2)
Improve this answer
3
  • This simply isn't working, but I looked into it further, and it appears that LEFT(B2,SEARCH(" – ",B2)) and RIGHT(B2,SEARCH(" – ",B2)) are retaining a space on either end, left over from the " – " segment. Thus, the strings of time I'm using have erroneous spaces in them.
    – purefusion
    Aug 9, 2011 at 16:55
  • Also, my strings of time use the format 12:00pm instead of Excel's desired 12:00 PM so that looks like it's throwing off the formula as well, but this seems like it would be extremely messy/complicated to work around. Any ideas?
    – purefusion
    Aug 9, 2011 at 16:59
  • I'll update my answer shortly to help you parse the time string. You'll need to add a space between the time and the AM/PM notation. Then use VALUE to let Excel interpret it as a time.
    – Ellesa
    Aug 9, 2011 at 17:00
0

The big issue is the time needs a space between the minutes and the am/pm: 1:00 am - 3:00 am or 9:00 pm - 12:00 am.

With this, you can use the formula:

=TIMEVALUE(RIGHT(A1,SEARCH(" - ",A1)))-TIMEVALUE(LEFT(A1,SEARCH(" - ",A1)))+
IF(TIMEVALUE(RIGHT(A1,SEARCH(" - ",A1)))-TIMEVALUE(LEFT(A1,SEARCH(" - ",A1)))<0,1,0)

And format the cell as a custom formatting h:mm:ss.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .