Time Conversion problem

Avatar

Please consider registering
Guest

Search

— Forum Scope —






— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

Register Lost password?
sp_Feed sp_topic_old
Time Conversion problem
Avatar
raihan84
Member
Forum Posts: 12
Member Since:
March 4, 2011
sp_UserOfflineSmall Offline
1
March 24, 2011 - 1:45 pm
sp_Permalink sp_Print

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

Avatar
Chad Johnson
Mod
Forum Posts: 867
Member Since:
August 11, 2011
sp_UserOfflineSmall Offline
2
March 24, 2011 - 3:52 pm
sp_Permalink sp_Print

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"

Avatar
raihan84
Member
Forum Posts: 12
Member Since:
March 4, 2011
sp_UserOfflineSmall Offline
3
March 24, 2011 - 4:20 pm
sp_Permalink sp_Print

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

Avatar
raihan84
Member
Forum Posts: 12
Member Since:
March 4, 2011
sp_UserOfflineSmall Offline
4
March 24, 2011 - 4:49 pm
sp_Permalink sp_Print

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?

Avatar
Chad Johnson
Mod
Forum Posts: 867
Member Since:
August 11, 2011
sp_UserOfflineSmall Offline
5
March 24, 2011 - 6:39 pm
sp_Permalink sp_Print

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]

Avatar
raihan84
Member
Forum Posts: 12
Member Since:
March 4, 2011
sp_UserOfflineSmall Offline
6
March 24, 2011 - 11:42 pm
sp_Permalink sp_Print

[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

Avatar
raihan84
Member
Forum Posts: 12
Member Since:
March 4, 2011
sp_UserOfflineSmall Offline
7
March 25, 2011 - 1:32 am
sp_Permalink sp_Print

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

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

Thanks

Avatar
Carol Bratt
Mod
Forum Posts: 66
Member Since:
August 11, 2011
sp_UserOfflineSmall Offline
8
March 25, 2011 - 10:54 am
sp_Permalink sp_Print

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)

Avatar
raihan84
Member
Forum Posts: 12
Member Since:
March 4, 2011
sp_UserOfflineSmall Offline
9
March 25, 2011 - 2:49 pm
sp_Permalink sp_Print

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

Avatar
Carol Bratt
Mod
Forum Posts: 66
Member Since:
August 11, 2011
sp_UserOfflineSmall Offline
10
March 25, 2011 - 3:10 pm
sp_Permalink sp_Print

Glad I could help!

Forum Timezone: America/Indiana/Indianapolis

Most Users Ever Online: 271

Currently Online:
19 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Member Stats:

Guest Posters: 10

Members: 2387

Moderators: 7

Admins: 4

Forum Stats:

Groups: 8

Forums: 19

Topics: 1802

Posts: 12962

Administrators: Jim Hillier, Richard Pedersen, David Hartsock, Marc Thomas

Moderators: Carol Bratt, dandl, Jason Shuffield, Jim Canfield, Terry Hollett, Dick Evans, Sergey Grankin

Scroll to Top

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!