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
Conan Kelly said:
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 said:
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