Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Working with Time and DateTime variables from Excel

    Hi,

    I noticed that whenever I import an excel file that contains a time or datetime variable onto Stata (time up to seconds), the values for those variables in Stata appear to be slightly off sometimes (generally by a second). I understand that there is a difference in the way Excel and Stata read dates and times and there might be some rounding issue at play here. I am looking for a way for Stata to read the excel time/datetime variables accurately. I see some similar questions and documentation online that address my question but I am still a little confused. Any help with this would be appreciated. Thank you!

  • #2
    I honestly don't know if this will help or not - it makes my head ache to read it - but the discussion shown by
    Code:
    help datetime_software##r5
    seems to have some guidance.

    The crux of the matter seems to be that Stata stores time values for a given date in milliseconds, while Excel stores them as a fraction of a day, and there's a formula that supposedly will adjust for this.

    Please let us know if this solves the problem.

    Comment


    • #3
      William, thanks for your response. I did come across the document you mention but it honestly was not of much help. My understanding is that the solutions it mentions are applicable for when one is not using "import excel" to import an excel file or more so for txt. or csv. files. I still did use the code in it to see if it would help but it didn't work. Another solution that I came across was this post here (https://www.stata.com/support/faqs/d...-value-behind/) that uses the round command. I used it on a time variable and it seemed to work but at the same time the solution seems too simplistic so I am not completely sure. It also only seems to work with a time variable, not a datetime variable.
      The one work around this problem is for me to create a string variable of the time/datetime variable in excel before importing it onto Stata and then converting it to a Stata time/datetime variable. However, this will be an issue if I have numerous excel files to deal with. Thanks again for your help.

      Comment


      • #4
        The solution you found is excellent - Stata should incorporate this FAQ into the reference I found.

        I do not understand why you say the solution using the round() function (it is not a command) does not work with datetime values - the example at the end of the post uses datetime values.

        In that example
        Code:
        replace Btime = round(Btime, 1)
        replaces the value of an existing datetime variable. If you want to create a new variable, in order to compare the two for example, then do remember that datetime values need to be stored as double, while Stata's default is float, so you would need something like
        Code:
        generate double BtimeR = round(Btime, 1)
        followed by the appropriate format command. Or
        Code:
        clonevar BtimeR = Btime
        replace BtimeR = round(BtimeR, 1)
        and BtimeR will have the same format as Btime, whatever that may be.

        Comment

        Working...
        X