# 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
###### Who is Participating?

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

Commented:
File is empty
0

Application 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

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", ""))
``````
0

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

OwnerCommented:
@als your sample file is 0 bytes in length
0

Author 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

Excel & VBA ExpertCommented:
Maybe this...
``````=(MOD(B2-A2,1))
``````
and then custom format the formula cells as [h]" hours" mm" Minutes"
1

OwnerCommented:
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

Application DeveloperCommented:
0

Author 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

Finance 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

Excel & VBA ExpertCommented:
I suggested the custom formatting only because I thought he wanted the text "Hours" and "Minutes" along with the numbers.
0

Author 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.