Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Time Conversion 1
- Thread starter f0rg3tfu1
- Start date
SkipVought
Programmer
Hi
=TIME(4,38,14)
...or enter
4:38:14
...in a cell
Faq68-5827
Skip,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my OLD subtlety...
for a NUance!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]
=TIME(4,38,14)
...or enter
4:38:14
...in a cell
Faq68-5827
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]I am sure Skip's solution will work, unless you actually have this in a cell:
[tt]4 hours 38 minutes 14 seconds[/tt]
If so, you would need another approach with text-to-columns transformations, concatenations of values, etc.
Have fun.
---- Andy
There is a great need for a sarcasm font.
[tt]4 hours 38 minutes 14 seconds[/tt]
If so, you would need another approach with text-to-columns transformations, concatenations of values, etc.
Have fun.
---- Andy
There is a great need for a sarcasm font.
SkipVought
Programmer
Ahhhh, and I missed something else as well. you wanted that time value converted to SECONDS.
So this...
=TIME(4,38,14)
...is the time value in DAYS. So it's then simple to convert DAYS to SECONDS as there are 24 HOURS in a DAY and 60 MINUTES in an HOUR and 60 SECONDS in a MINUTE.
=TIME(4,38,14)*24*60*60
And if you have ever wondered, "Should I multiply or divide?" use the UNITS as a guide...
[pre]
'
HOURS MINUTES SECONDS
DAYS * ----- * ------- * -------
DAY HOUR MINUTE
[/pre]
...and you see that the DAYS & MINUTES cancel out, leaving SECONDS
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
So this...
=TIME(4,38,14)
...is the time value in DAYS. So it's then simple to convert DAYS to SECONDS as there are 24 HOURS in a DAY and 60 MINUTES in an HOUR and 60 SECONDS in a MINUTE.
=TIME(4,38,14)*24*60*60
And if you have ever wondered, "Should I multiply or divide?" use the UNITS as a guide...
[pre]
'
HOURS MINUTES SECONDS
DAYS * ----- * ------- * -------
DAY HOUR MINUTE
[/pre]
...and you see that the DAYS & MINUTES cancel out, leaving SECONDS
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]- Thread starter
- #5
Thanks for the reply guys...
But this is actually what is in the cell:
Time to Resolution
1 minute 56 seconds
4 hours 38 minutes 14 seconds
3 hours 31 minutes 6 seconds
3 hours 30 minutes 6 seconds
3 hours 30 minutes 6 seconds
6 hours 32 minutes 7 seconds
It actually spits out of the database like that... I want to convert this into seconds so I need to write a formula to convert the hours and minutes and add that all up into seconds.
But this is actually what is in the cell:
Time to Resolution
1 minute 56 seconds
4 hours 38 minutes 14 seconds
3 hours 31 minutes 6 seconds
3 hours 30 minutes 6 seconds
3 hours 30 minutes 6 seconds
6 hours 32 minutes 7 seconds
It actually spits out of the database like that... I want to convert this into seconds so I need to write a formula to convert the hours and minutes and add that all up into seconds.
SkipVought
Programmer
Use Data > Text to Columns--DELIMITED using SPACE, to parse these cells as shown...
[pre]
1 minute 56 seconds
4 hours 38 minutes 14 seconds
3 hours 31 minutes 6 seconds
3 hours 30 minutes 6 seconds
3 hours 30 minutes 6 seconds
6 hours 32 minutes 7 seconds
[/pre]
Then in column G, this formula...
[tt]
G2: =IF(LEFT(B2,4)="hour",A2*60*60,IF(LEFT(B2,6)="minute",A2*60,A2))
+IF(LEFT(D2,6)="minute",C2*60,C2)
+E2
[/tt]
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
[pre]
1 minute 56 seconds
4 hours 38 minutes 14 seconds
3 hours 31 minutes 6 seconds
3 hours 30 minutes 6 seconds
3 hours 30 minutes 6 seconds
6 hours 32 minutes 7 seconds
[/pre]
Then in column G, this formula...
[tt]
G2: =IF(LEFT(B2,4)="hour",A2*60*60,IF(LEFT(B2,6)="minute",A2*60,A2))
+IF(LEFT(D2,6)="minute",C2*60,C2)
+E2
[/tt]
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]- Thread starter
- #7
Hi Skip,
I think I got it, but my only issue is that ("Time to Resolution" is column E) Sometimes it may only come up seconds, sometimes it may come up hours, minutes and seconds... sometimes "Days" might be the first. Does that matter?
This is exactly how the data is coming back in a CSV file, sample of 10 rows:
Incident Incident Type Opened Resolved/Closed Time to Resolution
rby-rw-rtr-2 Rural Wireless 2016-07-01 at 20:15 2016-07-01 at 20:17 1 minute 56 seconds
sva-rw-msc-1 Rural Wireless 2016-07-05 at 17:26 2016-07-05 at 22:04 4 hours 38 minutes 14 seconds
sva-rw-img-1 Rural Wireless 2016-07-05 at 17:27 2016-07-05 at 20:58 3 hours 31 minutes 6 seconds
sva-rw-rtr-2 Rural Wireless 2016-07-05 at 17:28 2016-07-05 at 20:58 3 hours 30 minutes 6 seconds
sva-rw-rtr-1 Rural Wireless 2016-07-05 at 17:28 2016-07-05 at 20:58 3 hours 30 minutes 6 seconds
pho-rw-msc-1 Rural Wireless 2016-07-06 at 09:18 2016-07-06 at 15:50 6 hours 32 minutes 7 seconds
pho-rw-img-1 Rural Wireless 2016-07-06 at 09:27 2016-07-06 at 13:15 3 hours 48 minutes 12 seconds
enm-rw-rtr-1 Rural Wireless 2016-07-06 at 12:36 2016-07-06 at 13:28 51 minutes 57 seconds
pho-rw-img-1 Rural Wireless 2016-07-06 at 13:31 2016-07-06 at 13:51 20 minutes 3 seconds
I think I got it, but my only issue is that ("Time to Resolution" is column E) Sometimes it may only come up seconds, sometimes it may come up hours, minutes and seconds... sometimes "Days" might be the first. Does that matter?
This is exactly how the data is coming back in a CSV file, sample of 10 rows:
Incident Incident Type Opened Resolved/Closed Time to Resolution
rby-rw-rtr-2 Rural Wireless 2016-07-01 at 20:15 2016-07-01 at 20:17 1 minute 56 seconds
sva-rw-msc-1 Rural Wireless 2016-07-05 at 17:26 2016-07-05 at 22:04 4 hours 38 minutes 14 seconds
sva-rw-img-1 Rural Wireless 2016-07-05 at 17:27 2016-07-05 at 20:58 3 hours 31 minutes 6 seconds
sva-rw-rtr-2 Rural Wireless 2016-07-05 at 17:28 2016-07-05 at 20:58 3 hours 30 minutes 6 seconds
sva-rw-rtr-1 Rural Wireless 2016-07-05 at 17:28 2016-07-05 at 20:58 3 hours 30 minutes 6 seconds
pho-rw-msc-1 Rural Wireless 2016-07-06 at 09:18 2016-07-06 at 15:50 6 hours 32 minutes 7 seconds
pho-rw-img-1 Rural Wireless 2016-07-06 at 09:27 2016-07-06 at 13:15 3 hours 48 minutes 12 seconds
enm-rw-rtr-1 Rural Wireless 2016-07-06 at 12:36 2016-07-06 at 13:28 51 minutes 57 seconds
pho-rw-img-1 Rural Wireless 2016-07-06 at 13:31 2016-07-06 at 13:51 20 minutes 3 seconds
SkipVought
Programmer
Well, YES! It does matter that day's could be included, and that the data is in column E!
You've been around here at Tek-Tips for some twelve years and can't use TGML tags to format your data in columns?
[pre]
Incident Incident Type Opened Resolved/Closed Time to Resolution
rby-rw-rtr-2 Rural Wireless 2016-07-01 at 20:15 2016-07-01 at 20:17 1 minute 56 seconds
sva-rw-msc-1 Rural Wireless 2016-07-05 at 17:26 2016-07-05 at 22:04 4 hours 38 minutes 14 seconds
sva-rw-img-1 Rural Wireless 2016-07-05 at 17:27 2016-07-05 at 20:58 3 hours 31 minutes 6 seconds
sva-rw-rtr-2 Rural Wireless 2016-07-05 at 17:28 2016-07-05 at 20:58 3 hours 30 minutes 6 seconds
sva-rw-rtr-1 Rural Wireless 2016-07-05 at 17:28 2016-07-05 at 20:58 3 hours 30 minutes 6 seconds
pho-rw-msc-1 Rural Wireless 2016-07-06 at 09:18 2016-07-06 at 15:50 6 hours 32 minutes 7 seconds
pho-rw-img-1 Rural Wireless 2016-07-06 at 09:27 2016-07-06 at 13:15 3 hours 48 minutes 12 seconds
enm-rw-rtr-1 Rural Wireless 2016-07-06 at 12:36 2016-07-06 at 13:28 51 minutes 57 seconds
pho-rw-img-1 Rural Wireless 2016-07-06 at 13:31 2016-07-06 at 13:51 20 minutes 3 seconds
[/pre]
You could have at least posted an example that exersize so ALL the possible conditions, like days.
[tt]
M2:
=IF(LEFT(F2,3)="day",E2*24*60*60,IF(LEFT(F2,4)="hour",E2*60*60,IF(LEFT(F2,6)="minute",E2*60,E2)))
+IF(LEFT(H2,4)="hour",G2*60*60,IF(LEFT(H2,6)="minute",G2*60,G2))
+IF(LEFT(J2,6)="minute",I2*60,I2)
+K2
[/tt]
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
You've been around here at Tek-Tips for some twelve years and can't use TGML tags to format your data in columns?
[pre]
Incident Incident Type Opened Resolved/Closed Time to Resolution
rby-rw-rtr-2 Rural Wireless 2016-07-01 at 20:15 2016-07-01 at 20:17 1 minute 56 seconds
sva-rw-msc-1 Rural Wireless 2016-07-05 at 17:26 2016-07-05 at 22:04 4 hours 38 minutes 14 seconds
sva-rw-img-1 Rural Wireless 2016-07-05 at 17:27 2016-07-05 at 20:58 3 hours 31 minutes 6 seconds
sva-rw-rtr-2 Rural Wireless 2016-07-05 at 17:28 2016-07-05 at 20:58 3 hours 30 minutes 6 seconds
sva-rw-rtr-1 Rural Wireless 2016-07-05 at 17:28 2016-07-05 at 20:58 3 hours 30 minutes 6 seconds
pho-rw-msc-1 Rural Wireless 2016-07-06 at 09:18 2016-07-06 at 15:50 6 hours 32 minutes 7 seconds
pho-rw-img-1 Rural Wireless 2016-07-06 at 09:27 2016-07-06 at 13:15 3 hours 48 minutes 12 seconds
enm-rw-rtr-1 Rural Wireless 2016-07-06 at 12:36 2016-07-06 at 13:28 51 minutes 57 seconds
pho-rw-img-1 Rural Wireless 2016-07-06 at 13:31 2016-07-06 at 13:51 20 minutes 3 seconds
[/pre]
You could have at least posted an example that exersize so ALL the possible conditions, like days.
[tt]
M2:
=IF(LEFT(F2,3)="day",E2*24*60*60,IF(LEFT(F2,4)="hour",E2*60*60,IF(LEFT(F2,6)="minute",E2*60,E2)))
+IF(LEFT(H2,4)="hour",G2*60*60,IF(LEFT(H2,6)="minute",G2*60,G2))
+IF(LEFT(J2,6)="minute",I2*60,I2)
+K2
[/tt]
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]
>It actually spits out of the database like that
Which isn't quite the same thing as it being HELD in the database like that. Indeed, from the data it would actually seem to be a caculated field. Do you have access to the query that is populating the spreadsheet. Or is someone running a report from the database that is exported as Excel?
Which isn't quite the same thing as it being HELD in the database like that. Indeed, from the data it would actually seem to be a caculated field. Do you have access to the query that is populating the spreadsheet. Or is someone running a report from the database that is exported as Excel?
>sometimes "Days" might be the first
Yet you did not include any sample data to represent this possibility. You could make it up by hand, at least.
Is it also possible to have something like this:[tt]
4 days 25 seconds[/tt]
or this situation would always be represented by:[tt]
4 days 0 hours 0 minutes 25 seconds[/tt]
???
Have fun.
---- Andy
There is a great need for a sarcasm font.
Yet you did not include any sample data to represent this possibility. You could make it up by hand, at least.
Is it also possible to have something like this:[tt]
4 days 25 seconds[/tt]
or this situation would always be represented by:[tt]
4 days 0 hours 0 minutes 25 seconds[/tt]
???
Have fun.
---- Andy
There is a great need for a sarcasm font.
SkipVought
Programmer
Actually, the answer is much simpler than this DAYS, HOURS, MINUTES, SECONDS silliness.
You already have the Opened and Resolved date/time, except some jerk added SPACE "at" SPACE between the date and time, probably someone's boss who wanted it to be "readable" rather than "usable"
So I replaced the SPACE "at" SPACE with a delimiter, added a column and parsed the Opened and Resolved each into date and time, like this...
[pre]
Incident Incident Type Opened oTIM Closed cTIM delta Seconds
rby-rw-rtr-2 Rural Wireless 7/1/2016 20:15 7/1/2016 20:17 0.001388889 120
sva-rw-msc-1 Rural Wireless 7/5/2016 17:26 7/5/2016 22:04 0.193055556 16680
sva-rw-img-1 Rural Wireless 7/5/2016 17:27 7/5/2016 20:58 0.146527778 12660
sva-rw-rtr-2 Rural Wireless 7/5/2016 17:28 7/5/2016 20:58 0.145833333 12600
sva-rw-rtr-1 Rural Wireless 7/5/2016 17:28 7/5/2016 20:58 0.145833333 12600
pho-rw-msc-1 Rural Wireless 7/6/2016 9:18 7/6/2016 15:50 0.272222222 23520
pho-rw-img-1 Rural Wireless 7/6/2016 9:27 7/6/2016 13:15 0.158333333 13680
enm-rw-rtr-1 Rural Wireless 7/6/2016 12:36 7/6/2016 13:28 0.036111111 3120
pho-rw-img-1 Rural Wireless 7/6/2016 13:31 7/6/2016 13:51 0.013888889 1200
[/pre]
Then I just convert days to seconds (days * 24*60*60)
It rub is that I disagree with every one of your hours, minutes, seconds be a few seconds, ALL a different number of seconds as you can see.
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
You already have the Opened and Resolved date/time, except some jerk added SPACE "at" SPACE between the date and time, probably someone's boss who wanted it to be "readable" rather than "usable"
So I replaced the SPACE "at" SPACE with a delimiter, added a column and parsed the Opened and Resolved each into date and time, like this...
[pre]
Incident Incident Type Opened oTIM Closed cTIM delta Seconds
rby-rw-rtr-2 Rural Wireless 7/1/2016 20:15 7/1/2016 20:17 0.001388889 120
sva-rw-msc-1 Rural Wireless 7/5/2016 17:26 7/5/2016 22:04 0.193055556 16680
sva-rw-img-1 Rural Wireless 7/5/2016 17:27 7/5/2016 20:58 0.146527778 12660
sva-rw-rtr-2 Rural Wireless 7/5/2016 17:28 7/5/2016 20:58 0.145833333 12600
sva-rw-rtr-1 Rural Wireless 7/5/2016 17:28 7/5/2016 20:58 0.145833333 12600
pho-rw-msc-1 Rural Wireless 7/6/2016 9:18 7/6/2016 15:50 0.272222222 23520
pho-rw-img-1 Rural Wireless 7/6/2016 9:27 7/6/2016 13:15 0.158333333 13680
enm-rw-rtr-1 Rural Wireless 7/6/2016 12:36 7/6/2016 13:28 0.036111111 3120
pho-rw-img-1 Rural Wireless 7/6/2016 13:31 7/6/2016 13:51 0.013888889 1200
[/pre]
Then I just convert days to seconds (days * 24*60*60)
It rub is that I disagree with every one of your hours, minutes, seconds be a few seconds, ALL a different number of seconds as you can see.
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]>?You already have the Opened and Resolved
Quite so. See my post concerning a calculated field.
>except some jerk added SPACE "at" SPACE between the date and time,
Worse than that, they've rounded the displayed time to minutes, so you cannot use it as the basis of a fresh calculation of the delta and expect seconds accuracy.
Quite so. See my post concerning a calculated field.
>except some jerk added SPACE "at" SPACE between the date and time,
Worse than that, they've rounded the displayed time to minutes, so you cannot use it as the basis of a fresh calculation of the delta and expect seconds accuracy.
SkipVought
Programmer
As strongm has pointed out, this would be a much more reasonable solution short of actually getting into the source data...
[pre]
Opened oTIM Closed cTIM delta days delts Minutes
7/1/2016 20:15 7/1/2016 20:17 0.001388889 2
7/5/2016 17:26 7/5/2016 22:04 0.193055556 278
7/5/2016 17:27 7/5/2016 20:58 0.146527778 211
7/5/2016 17:28 7/5/2016 20:58 0.145833333 210
7/5/2016 17:28 7/5/2016 20:58 0.145833333 210
7/6/2016 9:18 7/6/2016 15:50 0.272222222 392
7/6/2016 9:27 7/6/2016 13:15 0.158333333 228
7/6/2016 12:36 7/6/2016 13:28 0.036111111 52
7/6/2016 13:31 7/6/2016 13:51 0.013888889 20
[/pre]
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
[pre]
Opened oTIM Closed cTIM delta days delts Minutes
7/1/2016 20:15 7/1/2016 20:17 0.001388889 2
7/5/2016 17:26 7/5/2016 22:04 0.193055556 278
7/5/2016 17:27 7/5/2016 20:58 0.146527778 211
7/5/2016 17:28 7/5/2016 20:58 0.145833333 210
7/5/2016 17:28 7/5/2016 20:58 0.145833333 210
7/6/2016 9:18 7/6/2016 15:50 0.272222222 392
7/6/2016 9:27 7/6/2016 13:15 0.158333333 228
7/6/2016 12:36 7/6/2016 13:28 0.036111111 52
7/6/2016 13:31 7/6/2016 13:51 0.013888889 20
[/pre]
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]- Thread starter
- #14
Skip...
Seriously? Yes, 12 years I've been on here but I was on a plane, in a hurry, on a tiny screen, and not a fan of Tecumseh Markup Language. I hope this to your liking:
Column E
6 seconds
1 minute 14 seconds
7 minutes 12 seconds
2 hours 28 minutes 6 seconds
1 day 9 hours 5 minutes 9 seconds
strongm - The data source is proprietary software and I cannot access queries, tables or anything. It comes back on a webpage in HTML which I have to cut and paste into a CSV file. Happy?
Andrzejek - The example above is ALL possibilities. Days is very very rare which is why I didn't include it in the first example.
This is a system I inherited and it is a legacy report that I am stuck working with so please be understanding.
Seriously? Yes, 12 years I've been on here but I was on a plane, in a hurry, on a tiny screen, and not a fan of Tecumseh Markup Language. I hope this to your liking:
Column E
6 seconds
1 minute 14 seconds
7 minutes 12 seconds
2 hours 28 minutes 6 seconds
1 day 9 hours 5 minutes 9 seconds
strongm - The data source is proprietary software and I cannot access queries, tables or anything. It comes back on a webpage in HTML which I have to cut and paste into a CSV file. Happy?
Andrzejek - The example above is ALL possibilities. Days is very very rare which is why I didn't include it in the first example.
This is a system I inherited and it is a legacy report that I am stuck working with so please be understanding.
SkipVought
Programmer
I would guess that the since Opened and Resolved/Closed values are rounded to the minute, sans seconds, that the days, hours, minutes, seconds reflect the actual time difference.
Hence my solution posted 26 Oct 16 02:53 is the latest applicable.
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
Hence my solution posted 26 Oct 16 02:53 is the latest applicable.
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]-
1
- #16
James Wallace
Programmer
Such a lot of data guys, thanks....
ScottScott3
IS-IT--Management
SkipVought
Programmer
@ScottScott3, posting a link with no explanation is not the way that Tek-Tips works. A helpful post would direct the OP to a specific relevant resolution.
Why not take a few days and spend some time checking out how things work here at Tek-Tips. This is not your average tek forum.
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
Why not take a few days and spend some time checking out how things work here at Tek-Tips. This is not your average tek forum.
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]- Status
