Convert Date & Time to milliseconds in Excel

[swaroop710]

I have date in A1 (6/11/2020) and time in B1 (7:49:47 AM).  I am looking for a way to convert these to milliseconds.  I tried =(A1-DATE(1970,1,1))*86400 to convert the date value in A1, but it seems to show 

#VALUE!

The format for A1 is 'date' and B1 is 'custom'

Any suggestion is appreciated.

Date	START TIME	END TIME
6/11/2020	7:49:47	7:51:17

[#2]

@swaroop710 

Make sure that you format the cell with the formula as General.

[#3]

@swaroop710 

It looks like your date and time are kept as texts (left aligned), of so simply applying of another format won't help, you need to convert texts to actual date/time (aka numbers). Better to have small sample file to be sure.

[#4]

@swaroop710 

Hi ,

Looks like you are selecting the wrong cell for the formulae:

Try Changing it to A2, and also for milliseconds you need to multiply to 86400000

so formulae looks something like this:

(A2-DATE(1970,1,1))*86400000