Formula calculating time between 2 date/times

[rawr19911]

I am having a bit of issue calculating time between 2 dates.
hoping to do this in formula as the sheet is pretty long
here is an example

 

[Scott Huish]

You're having problems because those date/times aren't in a format that Excel recognizes as such.

Does this work for you?
=SUBSTITUTE(REPLACE(B2,LEN(B2)-1,0," ")," ",", ",2)-SUBSTITUTE(REPLACE(A2,LEN(A2)-1,0," ")," ",", ",2)

 

[rawr19911]

You're having problems because those date/times aren't in a format that Excel recognizes as such.

Does this work for you?
=SUBSTITUTE(REPLACE(B2,LEN(B2)-1,0," ")," ",", ",2)-SUBSTITUTE(REPLACE(A2,LEN(A2)-1,0," ")," ",", ",2)

got a #value error-
this is what i used
=SUBSTITUTE(REPLACE(L2,LEN(L2)-1,0," ")," ",", ",2)-SUBSTITUTE(REPLACE(J2,LEN(J2)-1,0," ")," ",", ",2)
i had to replace the a's ieth j's and the b's with L's

but it gives error back

 

[Scott Huish]

What's the error because it works for me:

Book1
	J	K	L	M
2	Oct 1 2023 3:44AM		Oct 2 2023 1:44AM	0.916667
Sheet1

Cell Formulas
Range	Formula
M2	M2	=SUBSTITUTE(REPLACE(L2,LEN(L2)-1,0," ")," ",", ",2)-SUBSTITUTE(REPLACE(J2,LEN(J2)-1,0," ")," ",", ",2)

 

[rawr19911]

What's the error because it works for me:

Book1
	J	K	L	M
2	Oct 1 2023 3:44AM		Oct 2 2023 1:44AM	0.916667
Sheet1

Cell Formulas
Range	Formula
M2	M2	=SUBSTITUTE(REPLACE(L2,LEN(L2)-1,0," ")," ",", ",2)-SUBSTITUTE(REPLACE(J2,LEN(J2)-1,0," ")," ",", ",2)

 

[rawr19911]

i can upload screenshot if needed

 

[rawr19911]

 

[rawr19911]

@Scott Huish i also have a quick question while you are helpign me with this- is there anywhere we can just donate money to the site or to the moderators ?

 

[Scott Huish]

Perhaps if you could download the xl2bb plugin and post an actual sample of your sheet, rather than an image, because my sample looks the same as your text and it works for me.

XL2BB - Excel Range to BBCode

 

[rawr19911]

Perhaps if you could download the xl2bb plugin and post an actual sample of your sheet, rather than an image, because my sample looks the same as your text and it works for me.

XL2BB - Excel Range to BBCode

company policy wont allow me it blocked me when i tried.