Time is endless

[Wil Moosa]

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?

 

[Andrew Poulsom]

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]

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

 

[Wil Moosa]

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.

 

[shift-del]

Hello Wil

I can't help you out on VBA, but this may help you as well:
<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]

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

Regards

Wil

 

[Andrew Poulsom]

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]

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

 

[Andrew Poulsom]

Please give some examples.

 

[Wil Moosa]

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