Making sense of imported time figure

kreases

Board Regular
Joined
Oct 26, 2005
Messages
111
Office Version
  1. 2016
Platform
  1. Windows
I am trying to get various totals out of data which I have had to copy and paste into Excel from another application, however the hours figure comes out strange if it goes over a day such as

1 15:06:43
or 1 01:00:54

Where the leading digit is number of days then its hh:mm:ss.

I would like to be able to convert this to either hh:mm:ss or seconds so that for instance the above figures would read

39:06:43
or 25:00:54

Any suggested calculation should be able to work if this figure goes over just 1 or 2 days, for example

4 01:00:54 would become 97:00:54

Please note in the pasted information there isn't a colon between the first digit and the hours and when the time is less than a day there isn't a leading seperate digit of zero you just get hh:mm:ss

I would appreciate any help you can give me thanks.

John.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hello John

Try pointing this formula at eg one of these values in A1 and copy down (assuming these values in the A column):

Code:
=IF(FIND(" ",A1&" ")<LEN(A1),LEFT(A1,FIND(" ",A1))+TRIM(MID(A1,FIND(" ",A1),255)),A1+0)

and format this column as:

[h]:mm:ss
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi John

Another option, in B1 (assuming the values in A:A as text):

Code:
=RIGHT(A1,8)+LEFT("0"&A1,LEN("0"&A1)-8)

Format B:B as [hh]:mm:ss

Hope this helps
PGC
Book1
ABCD
11201:00:54289:00:54
2401:00:5497:00:54
3101:00:5425:00:54
401:00:5401:00:54
5
Sheet1
 

kreases

Board Regular
Joined
Oct 26, 2005
Messages
111
Office Version
  1. 2016
Platform
  1. Windows
Results

I have tried both those methods as the length of formula is quite different,
Richard your formula works perfectly in all situations however I seem to have a problem with your formula PGC and not sure why but I get the following

17:37:30 17:37:30
03:04:05 ######
00:06:28 ######
00:06:04 ######
00:05:51 0:05:51

I have tried formatting both columns differently but it doesn't make any difference so I will go with Richards option.

Richard I assume the basis of your formula is to had the leading digit (day) to the remaining digits (hh:mm:ss) but what I don't understand is why just adding this to the hh:mm:ss section works out, how does Excel know that for instance the 1 is = to 24 hours or 2 is = to 48hrs etc

Any heads up on this might help in the future, thanks.

Thanks to both of you for helping me out.

John
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

John

It's all down to the way Excel stores date/time values - it stores them as a decimal number where the whole or integer part represents the number of days and the fractional part represents a portion of a day (ie hours/minutes/seconds). Thus 1 represents 24 hours, 0.5 represents 12:00:00 pm, 0.75 represents 18:00:00 etc.

Doe this explain it satisfactorily for you?
 

kreases

Board Regular
Joined
Oct 26, 2005
Messages
111
Office Version
  1. 2016
Platform
  1. Windows
Understood

Richard,

Thanks for that explanation, much appreciated.

I do have another calculation I need not sure if I should ask in another topic but see if you can help.

I now have a list of hh:mm:ss and I want to find the longest time in that list but only if it against a particular source, for example

Source duration
A 01:00:01
B 36:01:00
C 24:10:01
A 02:01:55
A 06:02:03

I want to find the longest duration for source A and would expect to get a result of 06:02:03 (hh:mm:ss)

Hope this makes sense, if you can't help I will put it inder a new subject heading,

Thanks again.

John
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

John

That's fairly straightforward with an array formula such as what follows (array formulas need to be confirmed with Ctrl+Shift+Enter, following which Excel will surround with curly braces (don't try and entrer these manually yourself)):
Excel Workbook
ABCD
1A01:00:01
2B36:01:00
3C24:10:01
4A02:01:55
5A06:02:03A06:02:03
Sheet3
 

kreases

Board Regular
Joined
Oct 26, 2005
Messages
111
Office Version
  1. 2016
Platform
  1. Windows
Thanks

Richard,

Thats terrific, much appreciated, thank you for your time.

Regards,

John
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi John

I'm glad your problem is solved.

I just wanted to tell you that my formula would only work if the times were imported as text, as I wrote in the post. It seems that for the times less than a day you have a real time in the cell. In this case my formula will not work. Richard's solution is better as it works in any case.

Kind regards
PGC
 

kreases

Board Regular
Joined
Oct 26, 2005
Messages
111
Office Version
  1. 2016
Platform
  1. Windows
Thanks

PGC,

Thanks for the follow up, much appreciated.

Regards,

John
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,758
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top