• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with Date stored as year followed by day number - 2021001, 2021002, 2021221 etc

skyh3ck

skyh3ck

Member
Hello

I need help with date stored as number

for example 01 January 2021 stored as 2021001
and same way for all the year like 31 Dec 2021 stored as 2021365

how can i change or with formula get the exact date in DD-Month-YYYY or something like that

please see attached file
 

Attachments

  • date as number.xlsx
    18.1 KB · Views: 14
Hey thanks for quick reply

i understood the left and right function, but how you just added "1" in month and it gives exact month from the value.

its working, but how is that possible, please help, becuase in the number value it only has year in first 4 number and number of day in last three, how it finds the correct month in that formula
 
skyh3ck
What is the smallest Month value? ... 1
What would be eg 66th day of year? January has 31 days ... February 28-29 days ... and rest days would be as days.
I didn't check all [im]possible combinations with Your ... 'dates' ( eg leap years ).
It would be more safe to use more normal dates or
to know - how those Your dates has calculated?
 
vletm said:
skyh3ck
What is the smallest Month value? ... 1
What would be eg 66th day of year? January has 31 days ... February 28-29 days ... and rest days would be as days.
I didn't check all [im]possible combinations with Your ... 'dates' ( eg leap years ).
It would be more safe to use more normal dates or
to know - how those Your dates has calculated?
Click to expand...

I respect you sir, i am just curious, how just using "1" get the correct month, the system we use has its unique number sorted this way, so when we retrieve the data we get this format, the system is designed like this only.

so all date is stored in system like this, so i just wanted to get the exact date in a separate column to look more presentable.

are you saying that this formula may not work with leap year ?
 
For those using Excel 365 the presentation may be radically different.
Code: 
= LET(
  yr, QUOTIENT(--dates,1000),
  d, MOD(--dates, 1000),
  DATE(yr,1,d) )
With insider beta, this may be expressed as a function defined using LAMBDA
Code: 
= DATEVALUEλ(dates)
where the function DATEVALUEλ is defined to be
Code: 
= LAMBDA(dt,
     LET(
     yr,  QUOTIENT(--dt,1000),
     d,   MOD(--dt, 1000),
     val, DATE(yr,1,d),
     val)
  )
 
Here's another simple option:

=DATE(LEFT(A2,4),1,1)+RIGHT(A2,3)-1

Then custom format the column as dd-mmmm-yyyy.
 
You must log in or register to reply here.
Back
Top