Convert Decimal number to time (1 Viewer)
- Thread starter JohnLee
- Start date
Hi Pr2-eugin,
Yes I was aware, what I am doing is performing a calculation of total hours and I want to display it in time hours, so I'm guessing I'm gonna have to brake it down into parts and then put it back together as a string, but looks like time format.
I hope I'm making sense here.
I have a From and To column which are both formatted for time and looks as follows:
From Column (F6)- 07:00:00
To Column (E6) - 11:30:00
I than have a minutes column which has the following formulae in it:
=HOUR(F6-E6)*60+MINUTE(F6-E6)
This converts the resulting time into decimal format and gives this result 270, which is what I expect.
I have column I which shows the time in proper time as follows:
04:30:00
I than have column J which shows the time in Decimal format as follows:
4.50
All of which is expected.
Now my problem is when it comes to summing up the total for Column J
My figures in decimal all work out fine, it's just the total ends up as follows for example:
4.50
2.00
5.25
7.00
in decimal the total is 25.75 which in proper time is 25 hours and 45 minutes, which looks like this: 25.75.
I want to convert this to look like this: 25:45:00
So My initial thoughts were to split this out so I have taken the function Left and obtained the left two characters as follows:
=LEFT(K106,2)
I than used the right function to obtain the right two characters as follows:
=RIGHT(K106,2)
So my idea is to convert the right two characters to proper time and this is where I am stuck because if I try this:
=RIGHT(k106,2)/1440
The result is 01:15:00 which is not what I want, because the formulae is treating the .75 (75) as a whole decimal number and therefore produces this result which on that understanding is correct, but not what I am trying to achieve.
If I change the right function to get the right 3 characters and apply the above formulae I get this result:
00:00:45, which is almost there except that the 45 really needs to be in the middle position like this 00:45:00.
And so therein lies my problem.
once I have been able to achieve that breakdown I can simple string the results together as a string, knowing that it is correct and I would string it together as follows:
=(L25 & ":" & M25 & ":" & "00")
Any assistance would be most appreciated
Regards
John
Yes I was aware, what I am doing is performing a calculation of total hours and I want to display it in time hours, so I'm guessing I'm gonna have to brake it down into parts and then put it back together as a string, but looks like time format.
I hope I'm making sense here.
I have a From and To column which are both formatted for time and looks as follows:
From Column (F6)- 07:00:00
To Column (E6) - 11:30:00
I than have a minutes column which has the following formulae in it:
=HOUR(F6-E6)*60+MINUTE(F6-E6)
This converts the resulting time into decimal format and gives this result 270, which is what I expect.
I have column I which shows the time in proper time as follows:
04:30:00
I than have column J which shows the time in Decimal format as follows:
4.50
All of which is expected.
Now my problem is when it comes to summing up the total for Column J
My figures in decimal all work out fine, it's just the total ends up as follows for example:
4.50
2.00
5.25
7.00
in decimal the total is 25.75 which in proper time is 25 hours and 45 minutes, which looks like this: 25.75.
I want to convert this to look like this: 25:45:00
So My initial thoughts were to split this out so I have taken the function Left and obtained the left two characters as follows:
=LEFT(K106,2)
I than used the right function to obtain the right two characters as follows:
=RIGHT(K106,2)
So my idea is to convert the right two characters to proper time and this is where I am stuck because if I try this:
=RIGHT(k106,2)/1440
The result is 01:15:00 which is not what I want, because the formulae is treating the .75 (75) as a whole decimal number and therefore produces this result which on that understanding is correct, but not what I am trying to achieve.
If I change the right function to get the right 3 characters and apply the above formulae I get this result:
00:00:45, which is almost there except that the 45 really needs to be in the middle position like this 00:45:00.
And so therein lies my problem.
once I have been able to achieve that breakdown I can simple string the results together as a string, knowing that it is correct and I would string it together as follows:
=(L25 & ":" & M25 & ":" & "00")
Any assistance would be most appreciated
Regards
John
Hi pr2-eurgin,
forgive me for being a bit dense, but I'm not writing code, but writing a formulae in a cell so would your code snippet translate to cell references
So for instance I have 25.75 in Cell reference G106, so how would I translate your code into a formulae.
using it as it is, even putting an equals sign in front doesn't work.
John
forgive me for being a bit dense, but I'm not writing code, but writing a formulae in a cell so would your code snippet translate to cell references
So for instance I have 25.75 in Cell reference G106, so how would I translate your code into a formulae.
using it as it is, even putting an equals sign in front doesn't work.
John
Hi pr2-eugin,
on entering your code/formulae as is but adding and equals sign at the begining in the cell, it brings up a message "too few arguments"
on entering your code/formulae as is but adding and equals sign at the begining in the cell, it brings up a message "too few arguments"
Code:
= Left("25.75", Instr("25.75", ".")-1) & ":" & Mid("25.75", Instr("25.75", "."))*60 & ":00"
pr2-eugin
Super Moderator
- Local time
- Today, 23:12
- Joined
- Nov 30, 2011
- Messages
- 8,494
I have not checked your file, but Excel does not have InStr function, the reason why your code fails. Here is the Excel version.
Replace A1, with the right Cell reference.
Code:
= LEFT(A1, FIND( ".",A1)-1) & ":" & MID(A1, FIND(".",A1),3)*60 & ":00"
