Import time duration from excel

[Rodrigo Badilla]

Hi all,

I have a problem when I import from excel a time duration variable (more than 24 hrs of duration). The string variable changed when I import to Stata:

Here
time start= Inicio VM
time end= Desconexion VM
duration= Horas

Lamentably when I import :

import excel using "Data.xlsx", allstring clear

Date (Fecha), change when I import from excel I can not calculate duration using start and end datetime.

I am import as string all database because I have 2 rows for name varible (its not a problem, fixed).

Lamentably this is the only way to show my problem. I can not show some dataex example.

[Andrew Musau]

Format the values as text in Excel before importing.

[Rodrigo Badilla]

Thanks , but not fix the problem, only change values but are not correct. I tried with differents formats:

[Rodrigo Badilla]

I fix the problem saving database as txt:

import delimited using "data", delim(";").

I get all the information as string.

Now my problem is convert to date and hours....

[Clyde Schechter]

Now my problem is convert to date and hours...

foreach v of varlist Inicio_VM__Fecha Desconexion_VM__Fecha {
    gen double _`v' = clock(`v', "DMYhm")
    assert missing(_`v') == missing(`v')
    format _`v' %tc
    drop `v'
    rename _`v' `v'
}

For the duration variable, a different approach is advisable. Assuming you want the duration denominated in minutes:

gen duration = clockdiff(Inicio_VM__Fecha, Desconexion_VM__Fecha, "m")

will do that. It will show a duration of, say, 75 minutes as 75, not as 1:15. But there is no Stata display format that will give you a numeric duration variable that looks like 75:00.

[Rodrigo Badilla]

Thanks Clyde Schechter I was looking for it.

Your command work great.
Now I will make a coffee...
Regards

Thanks to evereyone

[Nick Cox]

Rodrigo Badilla An alternative take here follows from reading your previous thread at https://www.statalist.org/forums/for...ur-and-minutes in which essentially the same problem was aired.

Same problem, same solution.

You have a duration in string form with the form hh:mm:ss or hhh:mm:ss so this shows how to get an answer in minutes.

clear 
input str8 whatever
"126:30:00"
"38:00:00"
"########"
end 

split whatever, parse(":") destring gen(dur) force 

gen wanted = 60 * dur1 + dur2 + dur3/60 

l 

     +----------------------------------------+
     | whatever   dur1   dur2   dur3   wanted |
     |----------------------------------------|
  1. | 126:30:0    126     30      0     7590 |
  2. | 38:00:00     38      0      0     2280 |
  3. | ########      .      .      .        . |
     +----------------------------------------+

An answer in hours or any other convenient unit should now be easy.

[Rodrigo Badilla]

Thanks Nick Cox , I got your solution in mind, I use your solution to compare results.

[Andrew Musau]

Thanks , but not fix the problem, only change values but are not correct. I tried with differents formats

Correct, changing the format will not achieve the desired conversion. Apart from saving the file as CSV and importing with import delimited, you can convert the duration to seconds by multiplying by 86400 (24 hours*60 minutes*60 seconds).

[Rodrigo Badilla]

Thanks Andrew Musau for you reply, in my post #4 I use import delimited and fix my format date and time duration as string.

Your excel solution its a good alternative and work fine to me if I divide by 3600 like my post #8.