'Calculate date & time difference between two cells (DD.MM.YYYY HH:MM:SS)
I am trying to find the difference in datetime between two cells from an imported data file.
I cannot seem to find the correct formula with the formatting.
DD.MM.YYYY HH:MM:SS
Is there anything I can do to help calculate without getting a #VALUE error? Attached below is a sample - the imported files often contain hundreds of entries.
Solution 1:[1]
You can get Days, Months, Year, Hours and Minutes as difference between two dates. DD/MM/YYYY itself is a date format. You get can get datetime difference with:
=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days, "&HOUR(B2-A2)&" hours, "&MINUTE(B2-A2)&" minutes and "&SECOND(B2-A2)&" seconds"
Solution 2:[2]
As You have DD.MM.YYYY format,
=DATEDIF(SUBSTITUTE( A2,".","/"), SUBSTITUTE(B2,".","/"),"y")&" years, "&DATEDIF(SUBSTITUTE(A2,".","/"),SUBSTITUTE(B2,".","/"),"ym")&" months, "&DATEDIF(SUBSTITUTE(A2,".","/"),SUBSTITUTE(B2,".","/"),"md")&" days, "&HOUR(SUBSTITUTE(B2,".","/") -SUBSTITUTE(A2,".","/") )&" hours, "&MINUTE(SUBSTITUTE(B2,".","/") -SUBSTITUTE(A2,".","/") )&" minutes and "&SECOND(SUBSTITUTE(B2,".","/") -SUBSTITUTE(A2,".","/") )&" seconds"
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 |