Forum

Notifications
Clear all

How do you turn a serial number into a date?

4 Posts
4 Users
0 Reactions
103 Views
(@usb)
Posts: 244
Honorable Member
Topic starter
 

In the attachment - I took a table that I brought from software into Excel
I downloaded all the unnecessary columns and left only the date
The date is written to me in a number - how do you turn it into a date structure ???

Thanks so much for the reply, Leah

 
Posted : 03/05/2021 7:22 pm
(@purfleet)
Posts: 412
Reputable Member
 

You want column B to look like column A?

202104 only has the year and month in it?

 
Posted : 03/05/2021 10:29 pm
Philip Treacy
(@philipt)
Posts: 1630
Member Admin
 

Hi Lea,

So the number 202104 is what you want to convert to a date?  I assume that represents April 2021 so there is no day specified, in which case I'll just ise the 1st of the month.

You can use this formula

=DATEVALUE(LEFT(TEXT(B2,"0"),4)&"/"&RIGHT(TEXT(B2,"0"),2)&"/01")

and set the cell to Date type - see attached.

regards

Phil

 
Posted : 03/05/2021 10:30 pm
(@debaser)
Posts: 837
Member Moderator
 

Just as an alternative formula:

=TEXT(B2,"0000-00""-01""")+0

then format as a Date.

 
Posted : 05/05/2021 4:35 am
Share: