Changing Time format error

HCW1966

New Member
Joined
May 6, 2016
Messages
20
Hi There,
When i import a file the hours come through as 37.30 (which is 37 hrs and 30 mins) and in number format. How do i change this to show 37.5 in number format? I have a list of about 40 different entries and when i sum them up in Excel the total is always less than it should be because of this issue. Any help would be appreciated thanks
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hello HCW

There are a number of solutions here. It would help if you could answer the following:
  1. What method are you using to import the data? POwerQuery/Macro/CopyPaste etc
  2. Does the source data show the hours as 37.30?
  3. Is the imported data TEXT or a NUMBER?
  4. Whats the range of numbers? Can you have 37.25? 37.00? 37? etc

If you don't mind having a helper column, this formula will do what you want (I think). Assume value is in Cell A1... can be copied down.
VBA Code:
=TRUNC(A1)+((A1-TRUNC(A1))*100)/60

Cheers
Caleeco
 
Upvote 0
Hi Caleeco and thanks for getting back to me.

1. First of all i export the file (it gives me the option to export as Excel Data), then i open Excel and go to the file i have exported and open the file.
2. The data source does show the hours as 37.30
3. The imported data is a number
4. The range is mostly .15 or .30 or .45 although i do see some .25

thanks
 
Upvote 0
Hi Caleeco and thanks for getting back to me.

1. First of all i export the file (it gives me the option to export as Excel Data), then i open Excel and go to the file i have exported and open the file.
2. The data source does show the hours as 37.30
3. The imported data is a number
4. The range is mostly .15 or .30 or .45 although i do see some .25

thanks

Hi, no worries.

Ok in that case the formula I posted should convert the values for you.
VBA Code:
=TRUNC(A1)+((A1-TRUNC(A1))*100)/60

If you wish to automate this, you would need to set up a Workbook that linked to the export file (Assuming it has the same name & file location), then you can add the formula as an additional column (which will be applied to new data rows as they are imported).
 
Upvote 0
You could also use this
=DOLLARDE(A2,60)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top