Time is endless

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
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

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Aug 11, 2002
Messages
882
"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
Joined
Aug 11, 2002
Messages
882
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
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 11, 2002
Messages
882
Shift-Del, "danke" for the response. I need a vba solution though as it fits better my requirements.

Regards

Wil
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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
Joined
Aug 11, 2002
Messages
882
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
Joined
Aug 11, 2002
Messages
882
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
 

Watch MrExcel Video

Forum statistics

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