# Time is endless

#### Wil Moosa

##### Well-known Member
I’m working with a worksheet with plenty of time entries. In fact most of the data is imported from a secondary programme. As most of the imported data fits well in the format of the cells of the worksheet ([h]:mm) I have trouble with one column.

While most of the imported data uses the format h:mm there is one column that uses a decimal format. As a result the calculation within the worksheet is not correct ánd I get an error message as [h]:mm  19:60 does not excist.

The question is: using the cell format [h]:mm and adding from a secondary source decimal data to cells in one column, what vba code could I use to adjust the data and reformat the column to get the correct data?

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Andrew Poulsom

##### MrExcel MVP
To convert decimal hours to a serial time divide by 24. You can put 24 in a spare cell and copy it. Then select your times and use Paste Special Divide.

#### Wil Moosa

##### Well-known Member
"24" worked. Did some trials with Paste Special Divide but to no result. Was able to find a work around using the divide by 24 idea.

Thank you Andrew.

Last edited:

#### Wil Moosa

##### Well-known Member
Somehow it doesn't bring what I need. I came up with a work around but I did not get it functional yet.

Again my dilemma. I have a column with time entries (e.g. 27:59). These entries use the devider : but the entries are not serial but decimal. Therefor the entry should be 27.59. The point is that I want to convert the decimal entry with : to a correct serial entry.

The idea I have to get there is as follows:

1. Convert the whole column from entries like 27:59 to 27.59
2. Multiply every entry in the column with 60 --> result is the number of minutes
3. Next step should be calculating back to hours and minutes with the right serial format.

Step 1 is easy;
Range("D3:D98").Select
Selection.NumberFormat = "[h],mm"

Step 2 could be archieved with Andrew's method but with me it does not work.

Step 3 --> I have no clue where to start.

Anyone here who can point me in the right direction? For a good understanding; we're talking vba code here.

Last edited:

#### shift-del

##### Well-known Member

Hello Wil

<table valign="middle" colspan="7" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="47,25pt"><col width="47,25pt"><col width="47,25pt"><col width="134,25pt"><col width="114,75pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="7" align="middle">Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="right">
</td><td align="left">days</td><td align="left">hours</td><td align="left">minutes</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="right">27:59</td><td align="right">1</td><td align="right">3</td><td align="right">59</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="right">
</td><td colspan="3" rowspan="1" align="middle">convert into decimal hours</td><td align="left">add up to a decimal value</td><td align="left">convert into serial time</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td align="right">
</td><td align="right">24</td><td align="right">3</td><td align="right">0,59</td><td align="right">27,59</td><td align="right">27:35</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>B2</td><td>=DAY(A2)</td></tr><tr><td>C2</td><td>=HOUR(A2)</td></tr><tr><td>D2</td><td>=MINUTE(A2)</td></tr><tr><td>B5</td><td>=B2*24</td></tr><tr><td>C5</td><td>=C2</td></tr><tr><td>D5</td><td>=D2/100</td></tr><tr><td>E5</td><td>=SUM(B5:D5)</td></tr><tr><td>F5</td><td>=E5/24</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created and displayed with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

#### Wil Moosa

##### Well-known Member
Shift-Del, "danke" for the response. I need a vba solution though as it fits better my requirements.

Regards

Wil

#### Andrew Poulsom

##### MrExcel MVP

Can you give some more examples of the time as as they appear and what they should be when converted. How did you manage to format a decimal value with a colon? Are they text entries?

#### Wil Moosa

##### Well-known Member
Hi Andrew,

I'm pretty sure the solution is in the information you gave already but I manage not to combine the right thoughts.

1. I import data from a secondary programme by saving it in Html format. Result is a spreadsheet with time entries like 22:59 and in cell A1 20:25.

2. Column A in this html spreadsheet has the same time format as all the other columns but... different from the other columns shows the minutes in decimals. Somehow the secondary programme uses in column A format h:mm where it should use format h.mm. The entry in cell A1 is 20:25

3. I import the data in a Excel worksheet with copy-paste. All pasted time entries are recognised as having the time format h:mm. Column A therefor shows wrong data.

4. Column A should show the correct data in the format [h]:mm, e.g. 22:59. Cell A1 should show 20:15

#### Wil Moosa

##### Well-known Member
Gorilla's in the mist... I lost you here. I thought I included some examples above.

Okay, your question was: "Can you give some more examples of the time as as they appear and what they should be when converted."

Well; 22:25 appears with h:mm format, should be converted to 22:15 [h]:mm format
22:50 apears with h:mm format, should be converted to 22:30 [h]:mm format

Replies
7
Views
219
Replies
1
Views
212
Replies
0
Views
394
Replies
9
Views
320
Replies
7
Views
489

1,130,108
Messages
5,640,139
Members
417,127
Latest member
shakilk

### 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.

### Which adblocker are you using?

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

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