Hello guys,
I have got a problem. I wanna change an integer number to corresponding hours and minutes. For example, if you enter 100 then it will return 1:40 hours, that is one hour and 40 minutes.
I like to have and user defined functions or macro code..............
Please help me with the conversion ASAP.
Thanks
Raihan
[quote="Ziggie":1bahq5ps]So...I'm not a macro coder, but your logic would look like this:
x = input number
if x < 60 return "x minutes"
else
y = x / 60 --gives number of hours
z = x-y -- gives number of minutes
return "y hours and z minutes"[/quote:1bahq5ps]
Yeh, you have got my logic but I wanna return as y:z format, where y is hours and z is min.
I have got two ways to solve this problem.................................
Let A1 = 100, now if we want to return value at A2 cell then at formula bar I have written the code
1. [code:3h4zvu1y]CONCATENATE(FLOOR(A1/60,1), ":",(A1/60-FLOOR(A1/60,1))*60)[/code:3h4zvu1y]
2. [code:3h4zvu1y]INT(A1/60)&":"&MOD(A1,60)[/code:3h4zvu1y]
in return in cell A2 i got 1:40.
Does anybody know any other formulas?
If you have a time in a decimal format, i.e. an integer and a fraction, e.g. 2.5 hours (2 and a half hours) and you want to convert to a standard time format 02:30 (2 hours and 30 minutes), then you can use a formula based on the TIME function with the following syntax:
TIME(hours,minutes,seconds)
Example:
We have a decimal time 2.5 in cell A2. we can use the following formula to convert it to a standard time:
=TIME(INT(A2);(A2-INT(A2))*60;0)
[quote="Carol Bratt":1ccho2kg]If you have a time in a decimal format, i.e. an integer and a fraction, e.g. 2.5 hours (2 and a half hours) and you want to convert to a standard time format 02:30 (2 hours and 30 minutes), then you can use a formula based on the TIME function with the following syntax:
TIME(hours,minutes,seconds)
Example:
We have a decimal time 2.5 in cell A2. we can use the following formula to convert it to a standard time:
=TIME(INT(A2);(A2-INT(A2))*60;0)
Jim Hillier
Richard Pedersen
David Hartsock
Carol Bratt
dandl
Jason Shuffield
Jim Canfield
Terry Hollett
Stuart Berg
John Durso
1 Guest(s)