Calculate and display elapsed time

[JGOpsMgr]

Good morning, I am trying to calculate the amount of time between 2 dates and display it in a format that would show years,months,days,hours, and mins. I would also like to calculate only using network days which I havnt even begun to address. for now, I have used the custom format function and tried yy:mm:dd:hh:mm but it seems inconsistent. Am I doing this the correct way or is there a better option?

 

[Sergius]

Is this what you need?

TRT.xlsx
	A	B	C
7	11/22/2024 9:16	12/23/2024 8:00	0:1:22:742:44
Sheet1

Cell Formulas
Range	Formula
C7	C7	=DATEDIF(A7,B7,"y")&":"&DATEDIF(A7,B7,"ym")&":"&ROUND(B7-DATE(YEAR(B7),MONTH(B7),1),0)&":"&TEXT(B7-A7,"[h]:mm")

 

[JGOpsMgr]

Everything looked good during formula eval until I got to hours and then the value error, when i copy it to the rest of the dates I get some value. I tried to download XL2BB but it keeps saying my document is protected even though it is not. So, I am afraid I am unable to send a mini-sheet for review.

11/26/2024 7:19 AM

11/26/2024 7:20 AM

#VALUE!

 

[JGOpsMgr]

When I simply deduct the finish time- the start time and use the formatting mentioned above and the time is substantial it looks right. The only exception is that it appears to be having a problem with the month. perhaps I am using the wrong format code?

11/18/2024 1:16 PM

11/18/2024 1:18 PM

00:01:00:00:01

5/27/2022 12:00 AM

10/11/2024 8:00 AM

02:05:17:08:00

 

[Sergius]

Here is the fixed formula

TRT.xlsx
	A	B	C
7	25.11.2024 9:16	25.11.2024 10:00	0:0:0:0:44
8	25.11.2024 9:16	25.12.2024 10:00	0:1:0:720:44
9	25.11.2024 9:16	25.12.2025 10:00	1:1:0:9480:44
Sheet1

Cell Formulas
Range	Formula
C7:C9	C7	=DATEDIF(A7,B7,"y")&":"&DATEDIF(A7,B7,"ym")&":" &DATEDIF(A7,B7,"md")&":"&TEXT(B7-A7,"[h]:mm")

 

[JGOpsMgr]

Ok it calculates now and the years , months,days is what i expected on the below but why is the minutes 20840? Sorry Hours

Started	Time Stamp of last move	Years, Months, Days, Hours, Mins
5/27/2022 12:00 AM	10/11/2024 8:00 AM	2:4:14:20840:00

 

[Sergius]

If you just want hours, replace "[h]" in the formula with "h".

 

[JGOpsMgr]

Perfect! Thank you so much!