agwalsh
asked on
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
EE_time_between_two_days.xlsx
als315
File is empty
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),
Modify the B1 and A1 accordinglyhours.xlsx
=CONCAT(TRUNC((B1-A1)*24),
Modify the B1 and A1 accordinglyhours.xlsx
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", ""))
@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.
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.
@als your sample file is 0 bytes in length
ASKER
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
EE_time_between_two_days02.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
use a datetime for A and B then you can simply @sum(B1-A1) for column C
EE_time_between_two_days-dwj.xlsx
EE_time_between_two_days-dwj.xlsx
Look at this
Copy-of-EE_time_between_two_days02.xlsx
Copy-of-EE_time_between_two_days02.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
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.
I suggested the custom formatting only because I thought he wanted the text "Hours" and "Minutes" along with the numbers.
ASKER
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.