TIME/DATE Calculation

[SFCChase]

I'm trying to calculate the total time between 2 dates/times and return the value in days, hours, min. Later on I will be calculation a running total of the values returned. I am unable to use =DATED (nothing shows up when I try type it in).

Any help would be appreciated

UNIT	DIVERT START	DIVERT END	DIVERT TIME
MSU	1/1/2025 0600	1/4/2025 1200	3, 6, 0
MSU	1/5/2025 1200	1/5/2025 1500	0, 3, 0
MSU	1/8/2025 0400	1/14/2025 2130	5, 17, 30
		TOTAL	9, 2, 30

Looking for a formula to fill D2 and D6. I can separate the dates and times if necessary. Eventually I will be looking to get D6 to calculate based off the specific unit label in Column A, but for now I'd be happy if it just added everything together.

Thanks in advance!

 

[LegendaryAxe]

Try this:

vbapractice.xlsm
	A	B	C
1	1/1/2025 6:00	1/4/2025 12:00	03 days 06 hours 00 minutes
2	1/5/2025 12:00	1/5/2025 15:00	00 days 03 hours 00 minutes
3	1/8/2025 4:00	1/14/2025 21:30	06 days 17 hours 30 minutes
Sheet7

Cell Formulas
Range	Formula
C1:C3	C1	=B1-A1

 

[MARK858]

=DATED

It should be =DATEDIF

Also are they real dates and times formatted to look as they do? asking because normally times have a : in them.
If they are real dates and times then you should be able to just subtract what looks like column B from column C

In the below E2 is formatted as dd"," hh","mm and E3 as d"," h", "m
Please note that my dates are in dd/mm/yyyy

Book1
	A	B	C	D
1	UNIT	DIVERT START	DIVERT END	DIVERT TIME
2	MSU	01/01/2025 06:00	04/01/2025 12:00	03, 06,00
3	MSU	01/01/2025 06:00	04/01/2025 12:00	3 , 6, 0
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	=C2-B2

Edit: and with total (you didn't carry over the hours in post one)

Book1
	B	C	D
1	DIVERT START	DIVERT END	DIVERT TIME
2	01/01/2025 06:00	04/01/2025 12:00	03, 06, 00
3	05/01/2025 12:00	05/01/2025 15:00	00, 03, 00
4	08/01/2025 04:00	14/01/2025 21:30	06, 17, 30
5
6		Total	10, 02, 30
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=C2-B2
D6	D6	=SUM(D2:D4)

 

[SFCChase]

It should be =DATEDIF

Also are they real dates and times formatted to look as they do? asking because normally times have a : in them.
If they are real dates and times then you should be able to just subtract what looks like column C from column D

In the below E2 is formatted as dd"," hh","mm and E3 as d"," h", "m
Please note that my dates are in dd/mm/yyyy

Book1
	A	B	C	D
1	UNIT	DIVERT START	DIVERT END	DIVERT TIME
2	MSU	01/01/2025 06:00	04/01/2025 12:00	03, 06,00
3	MSU	01/01/2025 06:00	04/01/2025 12:00	3 , 6, 0
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	=C2-B2

That's my mistake on the DATEDIF, evidently I didn't type correctly before moving on the the rest of the post.

What are you putting in E2 and E3? I can't see them in the table you posted (I don't have xl2bb due to security on our network).

The times I'm using are in military 24hr format but i'll try adding the : to see if it'll straighten things out.

 

[MARK858]

E2 and E3

My typo should be D2 and D3, formulas are below the tables, if they are real date/times it is simple subtraction and sum. formatting in last table is dd", " hh", "mm

 

[SFCChase]

Try this:

vbapractice.xlsm
	A	B	C
1	1/1/2025 6:00	1/4/2025 12:00	03 days 06 hours 00 minutes
2	1/5/2025 12:00	1/5/2025 15:00	00 days 03 hours 00 minutes
3	1/8/2025 4:00	1/14/2025 21:30	06 days 17 hours 30 minutes
Sheet7

Cell Formulas
Range	Formula
C1:C3	C1	=B1-A1

how did you get "days", "hours", "minutes" to show up? It keeps giving me a dd:HH:MM format in the result

 

[LegendaryAxe]

Highlight the cells you want to reformat, press ctrl+1, and then go to "Custom" and enter the following under "Type":