Converting Excel Time to Stata Time

[Amelia Mask]

Hello all,

I have a series of variables in excel that track how much time has elapsed. For example, 'Avg Talk Time' is the average amount of time spent on the phone, 'Total After Call Time' is the total amount of time spent on the phone after hours, and 'Total Aux/Other' is the total amount of time spent on the phone for other reasons. Ideally, I would like what is displayed in excel (in the table below) to be displayed in Stata after I import it. And I would like to be able to compute the totals of columns in Stata as I have done in Excel. How can I do this?

For example, it displays 56:50:54 in the cell, but the actual value of the cell is 1/2/1900 8:50:54 AM and this is what is displayed in Stata. How can I get Stata to display what is displayed in Excel?

Display in excel:

Avg Talk Time	Total After Call Time	Total Aux/Other
3:55	51:36:16	56:50:54
0:00	0:00	0:00
6:49	66:11:27	100:34:30
0:00	0:00	0:00
2:08	26:43:25	57:10:38
0:00	0:00	0:00
0:00	0:00	0:00

Total:

12:52

144:31:08

214:36:02

Display in Stata after importation.

AvgTalkTime	TotalAfterCallTime	TotalAuxOther
31dec1899 00:03:55	02Jan1900 3:36	02Jan1900 8:50
31dec1899 00:00:00	31dec1899 00:00:00	31dec1899 00:00:00
31dec1899 00:06:49	02Jan1900 18:11	04Jan1900 4:34
31dec1899 00:00:00	31dec1899 00:00:00	31dec1899 00:00:00
31dec1899 00:02:08	01Jan1900 2:43	02Jan1900 9:10
31dec1899 00:00:00	31dec1899 00:00:00	31dec1899 00:00:00
31dec1899 00:00:00	31dec1899 00:00:00	31dec1899 00:00:00

I have tried to import the data as a string, but it will take the value of the cell rather than the display of the cell.

I have tried the conversion from 1900s Excel time to Stata time, but after I do the formatting step, it still displays the time as this -1.63577e+20.
https://blog.stata.com/2011/01/05/us...ther-software/

I tried creating a baseline time variable of Excel in Stata as 31dec1899 00:00:00 and using that to subtract my time variables against, but when I try to create that time variable in Stata, I can only go back to 31dec1959 23:59:38.

*Note: I am using Stata 15

Thank you for your help,
Amelia

[Nick Cox]

Durations are often best not regarded as date-times at all. For example, if I run 100 m in 9 seconds (slight exaggeration of my skills there) there is little point in regarding that sprint duration as a date-time 9 seconds after midnight on 1 January 1960, regardless of whether I choose a display format that suppresses the irrelevant date. (Whether I could run faster on 1 January 1960 than I could now is not beyond all conjecture.)

I don't know how you're importing this exactly -- or what is held in Stata exactly -- because none of the advice at https://www.statalist.org/forums/help#stata is followed here.

Assuming that your times are hours:minutes:seconds then most crucially of all you have some times greater than 24:00:00. So far as I can work out there is no way within the date-time framework to show those correctly because Stata is like a 24 hour clock in that respect. Consider a duration of 24:35:46 (one just longer than 24 hours) I can't display this more closely than

. di %tcjjj:HH:MM:SS 24 * 60 * 60000 + 35 * 60000 + 46 * 1000
  2:00:35:46

but note that Stata shows this as a time on day 2, not as 1 day plus 35 min 46 s.

I can see a point in holding a numeric variable that is in seconds, because you want to do calculations with it. I can also see a point in holding times as string variables because they are more congenial for display.

Here's some token code generating times in seconds from strings of the form "[h]hh:mm:ss" or "mm:ss"

clear
input str9 (AvgTalkTime    TotalAfterCallTime    TotalAuxOther)
"3:55"    "51:36:16"    "56:50:54"
"6:49"    "66:11:27"    "100:34:30"
"2:08"    "26:43:25"    "57:10:38"
end

gen work = ""
quietly foreach v in AvgTalkTime    TotalAfterCallTime    TotalAuxOther {
   replace work = `v'  
   replace work = "0:" + `v' if 1 == (length(`v') - length(subinstr(`v', ":", "", .)))  
   split work, destring gen(`v') parse(":")
   gen `v'Num = 3600 * `v'1 + 60 * `v'2 + `v'3
   drop `v'1 `v'2 `v'3  
}
  
drop work

list

     +------------------------------------------------------------------+
     | AvgTal~e   TotalA~e   TotalAu~r   AvgTal~m   Tot~eNum   Tot~rNum |
     |------------------------------------------------------------------|
  1. |     3:55   51:36:16    56:50:54        235     185776     204654 |
  2. |     6:49   66:11:27   100:34:30        409     238287     362070 |
  3. |     2:08   26:43:25    57:10:38        128      96205     205838 |
     +------------------------------------------------------------------+

[Amelia Mask]

Thank you so much Dr. Cox,

I didn't clarify the exact process. The data comes from a third party SQL database. I can download the data as .xls or .csv. I then calculate some indicators and perform some analysis with the data. I wanted to automate that analysis process which is why I wanted to import it into Stata. I didn't post any Stata syntax because my syntax was only import excel using "file location", firstrow clear. I needed to get my elapsed time variables into a proper format that I could perform calculations and have it converted to be readable to humans and I wasn't sure what approach to take or what approaches were available.

Initially, I was importing the data into Stata from an .xls file. When I was doing this, Stata was pulling the value of the cell and, understandably, had automatically converted it to a time variable (which I agree isn't very handy for the situation). (Below are two images, one of the data in the .xls file before importation and one after importation to Stata.)

Image 1: Data in Excel.xls file before importation to Stata

Image 2: Data in Stata after importation from .xls file.

(1) The first step to working with the data was being able to import the values from excel as string, not as dates. I discussed with a colleague and they pointed out that a .csv file will be imported into Stata with the value displayed and not the value of the cell (See Below two images). So the first step is to convert the file to .csv or download the file as .csv.

Image 3: Data in Excel .csv file before importation to Stata

Image 4: Data in Stata after importation from .csv file.

(2) Now we can use Dr. Cox's excellent syntax to convert each of these elapsed time variables to seconds in order to perform calculations.

(3) I wanted to re-convert my completed calculations as hh:mm:ss as I think that will be more comprehensible. I downloaded egenmore from SSC. There is a command called elap(time) that can convert integer seconds to a dd:hh:mm:ss string. More can be found at this link: https://www.stata.com/statalist/arch.../msg00355.html I think that should be enough to complete the calculations I need to do in Stata. Thank you very much Dr. Cox for your help. Hopefully this will be helpful to others working with elapsed time variables from Excel to Stata.

[Nick Cox]

Thanks very much for the closure, but for future questions please do keep reading the FAQ Advice referenced in #2 and note that images are much less helpful than you might hope.

Oddly, or otherwise, I'd forgotten about the elap() function for egen which turns out to be a child of mine.

. ssc type _gelap.ado
*! 1.0.0 CFB 29 Sep 2002 
* 1.1.0 NJC 7 December 2000 _gbom
* 1.0.0  NJC 12 July 2000