How to covert numbers into date

[Muthu_Vinith]

Hi Experts, 

I have a list of dates in the field called my_date like below:

45123
45127
45130

How can I convert this? 

Thank you!

[ITWhisperer]

It depends on what this number is supposed to represent

[Muthu_Vinith]

I tried to convert it but i couldn't get the exact results. Are there any other ways to convert it @ITWhisperer ?

[PickleRick]

Again - what dates are these numbers supposed to be?

[Muthu_Vinith]

It’s supposed to be based on the data @PickleRick 

[ITWhisperer]

Can you give an example of what 45123 is supposed to be as a date? I can make a guess but it might be wrong which would waste everyone's time.

[Muthu_Vinith]

Format of the date you want based on that number? @ITWhisperer 

[ITWhisperer]

How about YYYY/mm/dd?

[Muthu_Vinith]

Like this @ITWhisperer 

YYYY-mm-dd

[ITWhisperer]

OK what date in YYYY-mm-dd format would you expect 45123 to be shown as?

[Muthu_Vinith]

I’m not sure. Give me an example so that I can try that @ITWhisperer 

[ITWhisperer]

If I give you a conversion, how will you know whether it is correct or not?

[Muthu_Vinith]

I tried this query but it's showing something like this. But when i checked with an excel for this number 45123 - it's showing as 07/16/23.  @ITWhisperer 

[ITWhisperer]

Finally, the key piece of information! You are expecting this to be an Excel date value.

| makeresults
| eval date=45123
| eval _time=(date-25567-2)*24*60*60

Excel uses dates based on the start of the 20th Century 1900-01-01, counting in days, whereas, Splunk uses unix-style times based on seconds since 1970-01-01, so, you need to subtract the number of days between these two baseline points, and multiply by the number of seconds in a day. Note that Excel may not be calculating the date correctly since it indexes the first day as 1 (instead of 0) and incorrectly assumes that 1900 was a leap year (which it wasn't), hence the extra -2 days in the calculation.

Having said that, you will have to decide whether the _time value returned is correct based on the source of your data i.e. it could be a couple of days out.

[Muthu_Vinith]

Ok Thanks @ITWhisperer