Problem with converting decimal time to hours and minutes

R

RobertSE6

Hi there - I hope someone can help a novice please!
I've tried searching but don't understand most of the answers so apologies
if this has already been answered

I have a spreadsheet which needs to display working hours per week in both
decimal and in hours and minutes. What I'm doing is taking the decimal result
and converting it to hours and minutes using this formula
=TEXT(G28/24,"h:mm") which works a treat ... unless the hours are more than
24

I'm sure there must be a simple solution - but I'm too simple to figure it
out - please help !

Rob
 
B

Bob Phillips

Use

=TEXT(G28/24,"[h]:mm")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

RobertSE6

Thanks Roger - just the job !

Roger Govier said:
Hi Robert

Try
=TEXT(G28/24,"[h]:mm")

--
Regards
Roger Govier



RobertSE6 said:
Hi there - I hope someone can help a novice please!
I've tried searching but don't understand most of the answers so apologies
if this has already been answered

I have a spreadsheet which needs to display working hours per week in both
decimal and in hours and minutes. What I'm doing is taking the decimal
result
and converting it to hours and minutes using this formula
=TEXT(G28/24,"h:mm") which works a treat ... unless the hours are more
than
24

I'm sure there must be a simple solution - but I'm too simple to figure it
out - please help !

Rob
Click to expand...
Click to expand...
 
R

RobertSE6

Perfect ! Thanks Bob - just for my learning - what is the square bracket
doing in that formula please

Rob

Bob Phillips said:
Use

=TEXT(G28/24,"[h]:mm")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



RobertSE6 said:
Hi there - I hope someone can help a novice please!
I've tried searching but don't understand most of the answers so apologies
if this has already been answered

I have a spreadsheet which needs to display working hours per week in both
decimal and in hours and minutes. What I'm doing is taking the decimal
result
and converting it to hours and minutes using this formula
=TEXT(G28/24,"h:mm") which works a treat ... unless the hours are more
than
24

I'm sure there must be a simple solution - but I'm too simple to figure it
out - please help !

Rob
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Robert

The [ ] around the hours, allows it to roll over past 24, instead of
restarting at 1 when it gets to 25

Using a format of Format>Cells>Number>Custom>[h]:mm does the same thing, as
compared to a format of hh:mm
--
Regards
Roger Govier



RobertSE6 said:
Thanks Roger - just the job !

Roger Govier said:
Hi Robert

Try
=TEXT(G28/24,"[h]:mm")

--
Regards
Roger Govier



RobertSE6 said:
Hi there - I hope someone can help a novice please!
I've tried searching but don't understand most of the answers so
apologies
if this has already been answered

I have a spreadsheet which needs to display working hours per week in
both
decimal and in hours and minutes. What I'm doing is taking the decimal
result
and converting it to hours and minutes using this formula
=TEXT(G28/24,"h:mm") which works a treat ... unless the hours are more
than
24

I'm sure there must be a simple solution - but I'm too simple to figure
it
out - please help !

Rob
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

It is telling Excel not to cycle the hours around the day, that is25 doesn't
cycle into 1, etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



RobertSE6 said:
Perfect ! Thanks Bob - just for my learning - what is the square bracket
doing in that formula please

Rob

Bob Phillips said:
Use

=TEXT(G28/24,"[h]:mm")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



RobertSE6 said:
Hi there - I hope someone can help a novice please!
I've tried searching but don't understand most of the answers so
apologies
if this has already been answered

I have a spreadsheet which needs to display working hours per week in
both
decimal and in hours and minutes. What I'm doing is taking the decimal
result
and converting it to hours and minutes using this formula
=TEXT(G28/24,"h:mm") which works a treat ... unless the hours are more
than
24

I'm sure there must be a simple solution - but I'm too simple to figure
it
out - please help !

Rob
Click to expand...
Click to expand...
Click to expand...
 
Top