Forum Discussion

swaroop710's avatar
swaroop710
Copper Contributor
Dec 17, 2020

Convert Date & Time to milliseconds in Excel

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.

DateSTART TIME END TIME
6/11/20207:49:477:51:17
excel
Formulas and Functions

3 Replies

Sort By
  • AkashCodes's avatar
    AkashCodes
    Copper Contributor
    May 27, 2021

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 19, 2020

    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.

Resources