This forum requires Javascript to be enabled for posting content
Log In
Please consider registering
Guest
Forum Scope


Match



Forum Options



Min search length: 3 characters / Max search length: 84 characters
Register Lost password?
Time Conversion problem
raihan84
12 Posts
(Offline)
1
March 24, 2011 - 1:45 pm

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

Chad Johnson
867 Posts
(Offline)
2
March 24, 2011 - 3:52 pm

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"

raihan84
12 Posts
(Offline)
3
March 24, 2011 - 4:20 pm

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

raihan84
12 Posts
(Offline)
4
March 24, 2011 - 4:49 pm

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?

Chad Johnson
867 Posts
(Offline)
5
March 24, 2011 - 6:39 pm

This will handle values under 60:
[code:29xp43hs]=IF(A1>60,CONCATENATE(TRUNC(A1/60), ":", A1-(TRUNC(A1/60)*60)),CONCATENATE("0:", A1))[/code:29xp43hs]

raihan84
12 Posts
(Offline)
6
March 24, 2011 - 11:42 pm

[quote="Ziggie":1rdot3s4]This will handle values under 60:
[code:1rdot3s4]=IF(A1>60,CONCATENATE(TRUNC(A1/60), ":", A1-(TRUNC(A1/60)*60)),CONCATENATE("0:", A1))[/code:1rdot3s4][/quote:1rdot3s4]

Thanks Zig

raihan84
12 Posts
(Offline)
7
March 25, 2011 - 1:32 am

This could be another formula.....................

[code:1hx7264w]TEXT(FLOOR(A1/60,1),"00")&":"&TEXT(MOD(A1,60),"00")[/code:1hx7264w]

Thanks

Carol Bratt
67 Posts
(Offline)
8
March 25, 2011 - 10:54 am

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)

raihan84
12 Posts
(Offline)
9
March 25, 2011 - 2:49 pm

[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)

Carol Bratt
67 Posts
(Offline)
10
March 25, 2011 - 3:10 pm

Glad I could help!

Forum Timezone: America/Indiana/Indianapolis
All RSSShow Stats
Administrators:
Jim Hillier
Richard Pedersen
David Hartsock
Moderators:
Carol Bratt
dandl
Jason Shuffield
Jim Canfield
Terry Hollett
Stuart Berg
John Durso
Top Posters:
Chad Johnson: 867
Mindblower: 664
carbonterry2: 356
Flying Dutchman: 278
grr: 211
Newest Members:
JudeLandry
benjaminlouis680309
drogers97439
travishead60
Gohighlevelsnapshots
Forum Stats:
Groups: 8
Forums: 20
Topics: 1941
Posts: 13516

 

Member Stats:
Guest Posters: 11
Members: 3177
Moderators: 7
Admins: 3
Most Users Ever Online: 2303
Currently Online:
Guest(s) 37
Currently Browsing this Page:
1 Guest(s)
Exit mobile version

WHY NOT SUBSCRIBE TO OUR NEWSLETTER?

Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!