Help with converting dates from fractional/decimal form to recognized form in Stata

[Madora Balchan]

Hey everyone,

I'm a student who needs help with this particular problem. I have in my Stata dataset a variable that is shown in fractional/decimal form to represent the date of birth of participants. I'm trying to convert them to an appropriate date variable that Stata can recognize, but for the life of me I have no clue on how to do so or where to start. The help file has everything on dates except in this way so I'm hoping to get any help that I can from here.

I have figured out how the decimal form was calculated, but do not know where to go from there, or how to compute this through Stata in the first place. I have been provided with an example, id #1 was born on 71.514 and it corresponds to 7th July 1971. Let us assume the year has 365.25 days, accounting for the leap year every 4 years.

Any help that I can get from this is very much appreciated!

[Clyde Schechter]

So, recall that a Stata internal format date variable is the number of days since 1jan1960. The year of your date is 1900 + the integer part. So, for example, just 71 would correspond to 1jan1971, which can be gotten as -mdy(1, 1, 1900+71)-. Then there is the fractional part, which can be multiplied by 365.25 to give the number of days into that year. So it boils down to being able to pull the integer and fractional parts out of the numbers. That's where the -floor()- and -mod()- functions come in.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float strange_date
71.514
end

gen stata_date = floor(mdy(1, 1, 1900+floor(strange_date)) + 365.25*mod(strange_date, 1))
format stata_date %td
list

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

Please also be aware that it is the custom here not to provide assistance with homework problems. You identified yourself as a student, which raises the question of whether this is a homework problem. Based on the question, this seems more like the kind of thing that comes up when working with some data set acquired for a thesis or dissertation than a typical homework problem. So I decided to respond. But if this was a homework problem, please don't do it again.

[Straso Jovanovski]

Hello, How do I get this in an hours:minutes:seconds format from a number stored as integer with decimal, for instance 44608.55576263? This is also an import from Excel format with base 1 Jan. 1900. I know 44608 translates to 18 Feb. 2022 . But then don't know how to deal with the decimal after (that is supposed to be hours,minutes,seconds of that day). di %tc decimaldate - 21914 produces: 01jan1960 00:00:22

[Maarten Buis]

Whenever you deal with empirical data you should consider the accuracy of the measurement. I know that for formal reasons the date of birth includes the time in minutes in Germany, but I always found that pretty silly. The births I have been present at where pretty messy processes. There is a formal/legal definition of when the baby is considered born. However, applying it to a given birth is not always straightforward (the baby moves in waves a bit out and back in again), nor is accurately applying that definition the biggest priority of the people involved (they have other things to do, because when the baby is formally born, the baby has not completely left the mother yet...).

So if you have date of birth in seconds or minutes, then those seconds and minutes are just random noise. The hours may contain some information.