Changing decimal time into 24 hour time and reverse

[Bobzter100]

Is it possible to change decimal time into 24hour time? For example, 1.75
hours in decimal in CELL A1 into 1:45 in 24 hour time in CELL B1.
Also - how would I reverse it? For example 2:30 24 hour time into 2.5
decimal time.
Also note that the decimal time would need to increment above 24:00 as the
totals will be above this value
Best Regards and thanks for looking.
Bobzter

 

[Conan Kelly]

bobzter100,

In cell B1, enter this formula:

=A1/24

....then format then give the cell the format "[h]:mm:ss;@" (that format is
the same thing as Format Cells dialog > Number tab > Category box > "Time"
category > Type: box > 37:30:55)

to go in revers, just multiply by 24 and format the cell as a number with
decimal places.

HTH,

Conan

 

[Bobzter100]

Hi Conan
Many thanks - that has provided me with a solution to what i thought was my
only problem! Any advice on the folowing concerning time?

I have entry cells formatted to [H]:mm for user input. This allows a user to
input any time, including a time greater than 24:00 hours. However, users
being users they tend not to enter what you want so in many instances they
enter, for example, 74.5 hours in decimal which shows up as 1788. Is there
any way i can use the adjacent column to convert this value into the correct
value of 74.5 (24 hour time)? Note that i've tried to restrict user input by
using validation through validate\time\between\ but this will only alow a
time span of 00:00 - 24:00 and essentially the user can enter any amout of
hours with no upper limit.
Best regards
Bobzter

bobzter100,

In cell B1, enter this formula:

=A1/24

....then format then give the cell the format "[h]:mm:ss;@" (that format is
the same thing as Format Cells dialog > Number tab > Category box > "Time"
category > Type: box > 37:30:55)

to go in revers, just multiply by 24 and format the cell as a number with
decimal places.

HTH,

Conan

Is it possible to change decimal time into 24hour time? For example, 1.75
hours in decimal in CELL A1 into 1:45 in 24 hour time in CELL B1.
Also - how would I reverse it? For example 2:30 24 hour time into 2.5
decimal time.
Also note that the decimal time would need to increment above 24:00 as the
totals will be above this value
Best Regards and thanks for looking.
Bobzter

 

[Pete_UK]

You might find this to be useful:

http://www.cpearson.com/excel/DateTimeEntry.htm

It enables you/your users to enter times without the colon.

Hope this helps.

Pete

Hi Conan
Many thanks - that has provided me with a solution to what i thought was my
only problem! Any advice on the folowing concerning time?

I have entry cells formatted to [H]:mm for user input. This allows a user to
input any time, including a time greater than 24:00 hours. However, users
being users they tend not to enter what you want so in many instances they
enter, for example, 74.5 hours in decimal which shows up as 1788. Is there
any way i can use the adjacent column to convert this value into the correct
value of 74.5 (24 hour time)? Note that i've tried to restrict user input by
using validation through validate\time\between\ but this will only alow a
time span of 00:00 - 24:00 and essentially the user can enter any amout of
hours with no upper limit.
Best regards
Bobzter

bobzter100,

In cell B1, enter this formula:

    =A1/24

....then format then give the cell the format "[h]:mm:ss;@" (that formatis
the same thing as Format Cells dialog > Number tab > Category box > "Time"
category > Type: box > 37:30:55)

to go in revers, just multiply by 24 and format the cell as a number with
decimal places.

- Show quoted text -

 

[David Biddulph]

If you want to convert from hours to Excel time, divide by 24 (as Excel
times are counted in days).

To prompt the users to use the right format when inputting the data, use the
Input message part of Data Validation, with a message such as:
Input hours and minutes in hh:mm format
--
David Biddulph

Hi Conan
Many thanks - that has provided me with a solution to what i thought was
my
only problem! Any advice on the folowing concerning time?

I have entry cells formatted to [H]:mm for user input. This allows a user
to
input any time, including a time greater than 24:00 hours. However, users
being users they tend not to enter what you want so in many instances they
enter, for example, 74.5 hours in decimal which shows up as 1788. Is there
any way i can use the adjacent column to convert this value into the
correct
value of 74.5 (24 hour time)? Note that i've tried to restrict user input
by
using validation through validate\time\between\ but this will only alow a
time span of 00:00 - 24:00 and essentially the user can enter any amout of
hours with no upper limit.
Best regards
Bobzter

bobzter100,

In cell B1, enter this formula:

=A1/24

....then format then give the cell the format "[h]:mm:ss;@" (that format
is
the same thing as Format Cells dialog > Number tab > Category box >
"Time"
category > Type: box > 37:30:55)

to go in revers, just multiply by 24 and format the cell as a number with
decimal places.

HTH,

Conan

Is it possible to change decimal time into 24hour time? For example,
1.75
hours in decimal in CELL A1 into 1:45 in 24 hour time in CELL B1.
Also - how would I reverse it? For example 2:30 24 hour time into 2.5
decimal time.
Also note that the decimal time would need to increment above 24:00 as
the
totals will be above this value
Best Regards and thanks for looking.
Bobzter