I have an excel column (imported from another tool) with times in the following Days:Hours:Minutes
However the Days are not displayed if 0
13:10
1:02:07
1:14:08
01:00
2:03:28
1:07:10
2:05:36
1:16:56
02:00
00:00
3:01:01
1:08:00
00:20
02:42
00:00
1:19:39
My end goal is to convert to Hours:Minutes so I can used the (Time Formula) Hour Formula, Minute Formula, Second Formula (Not Relevant)
Not sure where to go from here?
Any help greatly appreciated
The days don't have to be displayed to be counted. In Excel, time is a function as it relates to a percentage of a whole day, which is 1. Therefore, 12 hours is really .5 and it just appears as 00:30:00 because it's formatted that way.
I'd first begin by formatting all of the cells in the range to a custom format using Ctrl+1, then choosing Custom, and then putting dd:hh:mm:ss as the format. This will show the missing zeroes and confirm if the data was altered in the export. (Data alterations happen often when dumping from telecom software)
Once you confirm the data is accurate, simple addition will show you the data you need.
If instead you just want to see all the data as it related in purely hours etc, then choose a custom format such as [h]:mm:ss, which will show the total hours without spilling into days. The same can be done with [m] or [s] to show each cell as whichever total increment you choose.
Thank you... Here is where the wheels fell off.
I highlighted the column and hit Cntr+1 (Which popped up the Format Cells Dialog Box)
Changed Format to Custom and entered dd:hh:mm:ss
time.PNG
.... I am not seeing the full output. Still seeing
00:00
00:00
13:10
1:02:07
1:14:08
01:00
2:03:28
1:07:10
2:05:36
1:16:56
02:00
00:00
3:01:01
1:08:00
00:20
02:42
This indicates that the data was exported as string values, even though the cells aren't formatted as text.
You can force Excel to reconsider the formats by activating each cell by hitting F2 and Enter, or you could try another solution I've used in the past:
Type a 1 somewhere in a blank cell. Then copy that cell. Next, select the range of times, right click, and hit Paste Special.., choose the multiply option and hit okay.
Alternatively, you could also put in the next column =AH2*1 and then copy downwards.
Last edited by daffodil11; 10-21-2013 at 03:17 PM.
The problem with *1 is that it will work to convert 13:10 but it will treat 1:02:07 as 1 hour, 2 minutes, 7 seconds, so any straightforward conversion is problematic. Try this formula in an adjacent column
=IFERROR(LEFT(A2,LEN(A2)-6),0)+IFERROR(RIGHT(A2,5),0)
[assuming data starting at A2 - change as required]
and then format the new column as d:hh:mm
That should give you the correct values but note that the d part of d:hh:mm won't go higher than 31 so if your original data has days > 31 that won't work!
Possibly better to format as [h]:mm to that 1:02:07 would convert to 26:07
Last edited by daddylonglegs; 10-21-2013 at 03:32 PM.
Audere est facere
The F2 works on individual cells. However I have 19,000 rows. I am not able to highlight the column and hit F2 .
The Type a 1 somewhere in a blank cell. Then copy that cell. Next, select the range of times, right click, and hit Paste Special.., choose the multiply option and hit okay. (The format the Column to)
It is still messing up the Days and Hours and Minutes.. Everything works except the days.. (Days is confusing it)
Excel is confused --- I have some formats that are hh:mm and others that are dd:hh:mm
00:00
00:00
13:10
1:02:07
1:14:08
01:00
2:03:28
1:07:10
2:05:36
1:16:56
02:00
00:00
3:01:01
1:08:00
00:20
02:42
24:09:31
AHHHHHHHHHHHH (Nothing is simple anymore) LOL
I love a challenge.
I am stumpted
Please keep the ideas coming. Thank you for the responses to date.
Thank you Daddy Long Legs
This worked great
=IFERROR(LEFT(A2,LEN(A2)-6),0)+IFERROR(RIGHT(A2,5),0)
Then I converted Formatted Column (Ctrl + F1) dd:hh:mm
Exceptional. Thank you very very much!
Back in business
Awesome DLL!
Where were you when I needed you 10 years ago?
My final workaround which I used for years was pulling the whole string apart, converting to total seconds, and then back to decimal time.
Sheer genius.
Its still bombing on 17:11:02 dd:hh:mm (See Image)
time2.PNG
It is working great on
01:14:08
02:14:57
We are very close... what is missing?
I am double checking work
Last edited by dmcbrier; 10-21-2013 at 04:24 PM.
I'm not getting the same issue.
Can you dump your timestamps into a blank workbook and attach a dummy file so we can see?
time string conversion.xlsx
<--- I added this row manually (And manually entered 10:14:16 to test) Excel is trying to convert to data and time format ??? Not sure
I attached worksheet.
No idea what format is importing.
Yes, I see. Instead of DAY use INT as I did in my example.
DAY returns the day of the month. You want the count of days and since time is a decimal value of 1, then the INTeger of that number is the count of days.
INT(19.98564554654) = 19 days
INT(19:25:22:30) = 19 days
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks