Convert Date & Time to milliseconds in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2000044%22%20slang%3D%22en-US%22%3EConvert%20Date%20%26amp%3B%20Time%20to%20milliseconds%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2000044%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20date%20in%20A1%20(6%2F11%2F2020)%20and%20time%20in%20B1%20(7%3A49%3A47%20AM).%26nbsp%3B%20I%20am%20looking%20for%20a%20way%20to%20convert%20these%20to%20milliseconds.%26nbsp%3B%20I%20tried%26nbsp%3B%3CSPAN%3E%3D(A1-DATE(1970%2C1%2C1))*86400%20to%20convert%20the%20date%20value%20in%20A1%2C%20but%20it%20seems%20to%20show%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CTABLE%20width%3D%2259%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2259%22%3E%23VALUE!%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%20format%20for%20A1%20is%20'date'%20and%20B1%20is%20'custom'%3C%2FP%3E%3CP%3EAny%20suggestion%20is%20appreciated.%3C%2FP%3E%3CTABLE%20width%3D%22243%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2294%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ESTART%20TIME%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2285%22%3EEND%20TIME%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%2F11%2F2020%3C%2FTD%3E%3CTD%3E7%3A49%3A47%3C%2FTD%3E%3CTD%3E7%3A51%3A17%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2000044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2000575%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Date%20%26amp%3B%20Time%20to%20milliseconds%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2000575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F905779%22%20target%3D%22_blank%22%3E%40swaroop710%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20format%20the%20cell%20with%20the%20formula%20as%20General.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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
2 Replies

@swaroop710 

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

@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.