Forum Discussion
Solved
Difference datetime fields over 24hours
I am trying to subtract two excel date/time fields from each other to determine the difference.
the fields are in the format
| 04/08/2018 23:30 | 08/08/2018 10:25 |
I can take ColB away from ColA and get a result if the answer is under 24 hours. This is not working for my example where the difference is much longer than 24hours.
Has anyone got a formula or suggestion for how to get the correct answer
Regards
J
Hi JM_381
it depends on how you would like to see the results. Generally, a normal subtraction works in any case, no matter if there is a 24h gap.
So you probably want to use custom number format [h]:mm
Kind regards,
Martin
Hi JM_381
it depends on how you would like to see the results. Generally, a normal subtraction works in any case, no matter if there is a 24h gap.
So you probably want to use custom number format [h]:mm
Kind regards,
Martin
- Thanks Martin
It was the number format that was throwing me. I went with [h]:mm and it worked perfectly.
J
IF you "take ColB away from ColA" that means A-B you will get a negative number but regardless the point is you are looking at just under a 4 day or 4 month difference depending on mm/dd or dd/mm format. If we assume the 4 day (much easier to work with) then the value should be 3.45... where 3 is the number of days and .45... is the fraction of a day.
So you can then go into custom formatting and choose under the time to display in HH:MM:SS and show 82:55:00
or you can separate the days and hours and format how you want.
- Thanks for your response
