Get date difference in seconds

[Clamarc]

Hi,

My Excel is Prefessional plus 2016

How do I get the difference between two dates in seconds?
date1: 2024-06-21 11:20:35.740
data2: 2024-06-21 10:58:51.110

Note: dates are in the format yyyy/mm/dd hh:mm:ss,000

Thank's

 

[hagia_sofia]

If you subtract those dates and multiply the result by 86400 (i.e. 24*60*60), then you should obtain the result.

 

[Clamarc]

If you subtract those dates and multiply the result by 86400 (i.e. 24*60*60), then you should obtain the result.

It didn't work for me!... could you give more details about your answer?
see image

 

[Cubist]

Confirm those are date/time data types and not text with =ISNUMBER(B3) . It should return TRUE. Other than that, @hagia_sofia suggestion works fine as long as you're subtracting big minus small.

Book1
	B	C	D
3	2024-06-21 11:20:35.740	2024-06-21 10:58:51.110	1,304.63
Sheet1

Cell Formulas
Range	Formula
D3	D3	=(B3-C3)*86400

 

[Alex Blakenburg]

What is your regional setting for the decimal delimiter ? Is it a comma or a period ?
Your copied in data has a period "2024-06-21 11:20:35.740" but you say your custom format is a comma "yyyy/mm/dd hh:mm:ss,000"
Also your image indicates your dates are left aligned which is @Cubist point about checking whether they are numbers or text.

Assuming you need a comma delimiter ideally you want to fix columns B & C, how will depend on how you source your data.
A work around or proof of concept might be

=(SUBSTITUTE(B3,".",",")-SUBSTITUTE(C3,".",","))*86400

 

[Clamarc]

What is your regional setting for the decimal delimiter ? Is it a comma or a period ?

Hi,
I'm from Brazil, my regional delimiter for decimal is the comma.

Another detail, when I click on the cell, the value in the formula bar appears rounded. How do I make the value appear in the formula bar the same as what is in the cell, see image.

 

[Clamarc]

Confirm those are date/time data types and not text with =ISNUMBER(B3) . It should return TRUE. Other than that, @hagia_sofia suggestion works fine as long as you're subtracting big minus small.

Worked, thank you!

 

[Clamarc]

If you subtract those dates and multiply the result by 86400 (i.e. 24*60*60), then you should obtain the result.

Worked, thank you!

 

[Alex Blakenburg]

Hi,
I'm from Brazil, my regional delimiter for decimal is the comma.

Another detail, when I click on the cell, the value in the formula bar appears rounded. How do I make the value appear in the formula bar the same as what is in the cell, see image.

Unfortunately the formula bar not showing the decimal for seconds is a recognised issue that has been around for a long time.
I don't believe there is a solution for it.

Fractions of Seconds – Daily Dose of Excel

dailydoseofexcel.com

 

[Clamarc]

Unfortunately the formula bar not showing the decimal for seconds is a recognised issue that has been around for a long time.
I don't believe there is a solution for it.

Fractions of Seconds – Daily Dose of Excel

dailydoseofexcel.com

ok...

One last question, in my Excel log file, when I check the date cells, they appear as ISNUMBER = False, but if I clear the cell and type the dates again and check, they appear as ISNUMBER = True. How do I make the dates in my Excel log file have ISNUMBER = True without having to re-enter the dates? See attached image.

thanks's