Home

Date formatted as text

%3CLINGO-SUB%20id%3D%22lingo-sub-869237%22%20slang%3D%22en-US%22%3EDate%20formatted%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CU%3EWhat%20do%20I%20need%20to%20do%20to%20get%20Excel%20to%20recognize%20the%20following%20as%20a%20date%20(i.e.%20number%20of%20days%20since%20Jan%201%2C%201900)%2C%20so%20I%20can%20do%20calculations%20(shift%20from%20UTC%20to%20PDT%2C%20delta%20t%20between%20measurements%2C%20etc)%3F%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E9%2F3%2F2019%203%3A33%3A00%20PM%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EI%20tried%20simply%20setting%20the%20format%20to%20Date%20and%20then%20tried%20%3DDATEVALUE(F2)%2C%20but%20still%20get%20%23VALUE!%20as%20the%20output.%20%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EThanks%20in%20advance%20if%20you%20think%20you%20might%20know%20the%20answer!%3C%2FU%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-869237%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-869352%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20formatted%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413219%22%20target%3D%22_blank%22%3E%40mneuner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20you%20tried%20this%3F%3C%2FP%3E%3CP%3E%3DINT(F2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-870728%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20formatted%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-870728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20suggestion%2C%20but%20that%20didn't%20work%20for%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E
mneuner
New Contributor

What do I need to do to get Excel to recognize the following as a date (i.e. number of days since Jan 1, 1900), so I can do calculations (shift from UTC to PDT, delta t between measurements, etc)?

 

9/3/2019 3:33:00 PM

 

I tried simply setting the format to Date and then tried =DATEVALUE(F2), but still get #VALUE! as the output.

 

Thanks in advance if you think you might know the answer!

2 Replies

@mneuner 

Have you tried this?

=INT(F2)

@Subodh_Tiwari_sktneer 

 

Thanks for your suggestion, but that didn't work for me.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies