# 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?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.