How do I show the difference in times when they are on two different days?

I have two columns with times (see attached file). The issue is that for these records the Time ANP is actually on the next day after the Time Triaged? How do I get it to show the number of hours as opposed to it having a hissy fit :-) as per attached file. Thanks
EE_time_between_two_days.xlsx
LVL 1
agwalshAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Can we assume that the later time is always the next day, ie only one midnight threshold crossed, and durations will be less than 24 hours?

If so, then it should be fairly simple to cover both scenarios:
 1 - where finish time is next day and therefore effectively earlier time-stamp
 2 - where finish time is same day and later time-stamp

Try this formula:
=IF(B2>A2,B2-A2,1-(A2-B2))

Assumes Times do not have the date element.
0
 
als315Commented:
File is empty
0
 
Juan OcasioApplication DeveloperCommented:
If you have A time like 1/20/2018  10:00:00 AM in Cell A1 and time like 1/20/2018  1:25:00 PM in Cell B1 this should work

=CONCAT(TRUNC((B1-A1)*24),":",MOD((B1-A1)*24*60,60))

Modify the B1 and A1 accordinglyhours.xlsx
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Shaun KlineLead Software EngineerCommented:
If your date/times are in a1 & b1, this formula will spell out each time component:
=IF(DAY(B1-A1) > 0, DAY(B1-A1) & " day" & IF(DAY(B1-A1) > 1, "s ", " "), "") & IF(HOUR(B1-A1) > 0, HOUR(B1-A1) & " hour" & IF(HOUR(B1-A1)> 1, "s ", " "), "") & IF(MINUTE(B1-A1)>0, MINUTE(B1-A1) & " minute" & IF(MINUTE(B1-A1) > 1, "s ", " "), "") & IF(SECOND(B1-A1)>0, SECOND(B1-A1) & " second" & IF(SECOND(B1-A1) > 1, "s", ""))

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@als
Were you able to open the file?
When I tried to download and open the sample file, it said, "Cannot open the file as file extension is invalid". That's strange.
0
 
David Johnson, CD, MVPOwnerCommented:
@als your sample file is 0 bytes in length
0
 
agwalshAuthor Commented:
That is very strange. I'll try again.  I did think that the date would be needed. The date is not in the column, so could I use some sort of If function to calculate it?
EE_time_between_two_days02.xlsx
0
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Maybe this...
=(MOD(B2-A2,1))

Open in new window

and then custom format the formula cells as [h]" hours" mm" Minutes"
1
 
David Johnson, CD, MVPOwnerCommented:
use a datetime for A and B then you can simply @sum(B1-A1) for column C
EE_time_between_two_days-dwj.xlsx
0
 
Juan OcasioApplication DeveloperCommented:
0
 
agwalshAuthor Commented:
@Rob Henson and @Subodh Tiwari (Neeraj) - thank you both for your elegant solutions. I went with Rob's one simply because the fewer things to explain the better :-) Loved them both though.
0
 
Rob HensonFinance AnalystCommented:
Glad to be of help. Neeraj's solution doesn't have to have the custom format, it still works with standard time formatting and gives the same result as my suggestion.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I suggested the custom formatting only because I thought he wanted the text "Hours" and "Minutes" along with the numbers.
0
 
agwalshAuthor Commented:
The solution solved the problem I had which was around the calculation to do when I just had the time (date would not be available) and the time was on two different days.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.