Hello,
I'm trying to find the difference in date/time 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.
Thanks in advance
CodePudding user response：
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"
CodePudding user response：
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"