Translating dates / times in decimal form from Excel

[Straso Jovanovski]

Hello,
I have a dataset imported from Excel which contains variables that are date / time combination. They would be in the format for instance "18feb2022 15:23:04". However, in Stata the cell values appear as this "44608.5557626273" stored as type str16, format %-9s.
I have successfully translated the integer part prior to the decimal point, using the Excel date format with a base of 1 Jan. 1900. di %td date after subtraction 44608 - 21914 produces 18feb2022. But then don't know how to deal with the decimal after (that should be hh:mm:ss of that day). di %tc decimaldate - 21914 produces: 01jan1960 00:00:22
Thank you for any help.

[Daniel Shin]

Stata uses millisecond input for date/time.

. * INITIAL VALUE
. di strofreal(44608.5557626273,"%td")
17feb2082

. 
. * OFFSET USING EXCEL BASE
. di strofreal(44608.5557626273-21914,"%td")
18feb2022

. 
. * DATE/TIME
. * 1/1/1960 00:00:00
. di strofreal(0,"%tc")
01jan1960 00:00:00

. 
. * 1/1/1960 12:00:00
. di strofreal(86400000 * 0.5,"%tc")
01jan1960 12:00:00

. 
. * 1/2/1960 00:00:00
. di strofreal(86400000,"%tc")
02jan1960 00:00:00

. 
. * YOUR DATE
. di strofreal((44608.5557626273-21914)*86400000,"%tc")
18feb2022 13:20:17

It's a bit odd that I am getting 13:20:17 in instead of 15:23:04, however.

[Daniel Shin]

How Stata would interpret your true datetime:

. * YOUR DATE/TIME AS STATA MS VALUE
. di %14.0f clock("18feb2022 15:23:04 ","DMYhms")
 1960816984000

. 
. * AS STATA DATE VALUE
. di (clock("18feb2022 15:23:04 ","DMYhms"))/86400000
22694.641

[Straso Jovanovski]

Thank you very much. It is very helpful. I simply made up the time "15:23:04" - it does not correspond to a real value (just looking at the decimal 0.55 thought that should be in the afternoon sometime, if the 24-hour day was on a scale 0-1). In any case, my 'guess' was kind of close to what you arrived at.