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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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)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
Rob HensonFinance 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

Your issues matter to us.

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

Start your 7-day free trial
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
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.