h:mm to decimal value
 

Good morning,

Is there a formula or easy way to automatically convert a custom h:mm format
to a decimal format?

Situation:
I am working on putting together an Excel Template for my boss for our
departmental time sheets. I currently have these basic categories setup
(worded differently but will use descriptive terms in this example).

Day AM TIME IN, AM TIME OUT, PM TIME IN, PM TIME OUT, Total (h:mm
format), and then a Total column (Decimal format)

Currently I have the AM, PM cells and Total h:mm cells formatted as "custom"
and set to "h:mm". And the Total Decimal Column format is set to "Number" and
to 2 decimal places. Reason for 2 total columns is to sum the h:mm values to
get a total value, and payroll needs the values in a decimal format (e.g.
8.25 hours).
Suggestions would be greatly appreciated.

Cordially,

It's easier than you think. Just multiply the answer by 24 and format to
general (or number).

Example:
Assuming AM TIME IN is in B1 and total is in F1
F1=(C1-B1+E1-D1)
Format as time (h:mm)
G1=24*F1
Format as general

"Brent E" wrote:

Good morning,

Is there a formula or easy way to automatically convert a custom h:mm format
to a decimal format?

Situation:
I am working on putting together an Excel Template for my boss for our
departmental time sheets. I currently have these basic categories setup
(worded differently but will use descriptive terms in this example).

Day AM TIME IN, AM TIME OUT, PM TIME IN, PM TIME OUT, Total (h:mm
format), and then a Total column (Decimal format)

Currently I have the AM, PM cells and Total h:mm cells formatted as "custom"
and set to "h:mm". And the Total Decimal Column format is set to "Number" and
to 2 decimal places. Reason for 2 total columns is to sum the h:mm values to
get a total value, and payroll needs the values in a decimal format (e.g.
8.25 hours).
Suggestions would be greatly appreciated.

Cordially,

If time is in A1 then =HOUR(A1)+MINUTE(A1)/60 will work if formatted as General
--
Gary''s Student


"Brent E" wrote:

Good morning,

Is there a formula or easy way to automatically convert a custom h:mm format
to a decimal format?

Situation:
I am working on putting together an Excel Template for my boss for our
departmental time sheets. I currently have these basic categories setup
(worded differently but will use descriptive terms in this example).

Day AM TIME IN, AM TIME OUT, PM TIME IN, PM TIME OUT, Total (h:mm
format), and then a Total column (Decimal format)

Currently I have the AM, PM cells and Total h:mm cells formatted as "custom"
and set to "h:mm". And the Total Decimal Column format is set to "Number" and
to 2 decimal places. Reason for 2 total columns is to sum the h:mm values to
get a total value, and payroll needs the values in a decimal format (e.g.
8.25 hours).
Suggestions would be greatly appreciated.

Cordially,

Excellent. I tried both of these suggestions and they work fabulously.
Thanks. I really appreciate the suggestions; have a fabulous day.

"Gary''s Student" wrote:

If time is in A1 then =HOUR(A1)+MINUTE(A1)/60 will work if formatted as General
--
Gary''s Student


"Brent E" wrote:

Good morning,

Is there a formula or easy way to automatically convert a custom h:mm format
to a decimal format?

Situation:
I am working on putting together an Excel Template for my boss for our
departmental time sheets. I currently have these basic categories setup
(worded differently but will use descriptive terms in this example).

Day AM TIME IN, AM TIME OUT, PM TIME IN, PM TIME OUT, Total (h:mm
format), and then a Total column (Decimal format)

Currently I have the AM, PM cells and Total h:mm cells formatted as "custom"
and set to "h:mm". And the Total Decimal Column format is set to "Number" and
to 2 decimal places. Reason for 2 total columns is to sum the h:mm values to
get a total value, and payroll needs the values in a decimal format (e.g.
8.25 hours).
Suggestions would be greatly appreciated.

Cordially,

You are welcome!
--
Gary''s Student


"Brent E" wrote:

Excellent. I tried both of these suggestions and they work fabulously.
Thanks. I really appreciate the suggestions; have a fabulous day.

"Gary''s Student" wrote:

If time is in A1 then =HOUR(A1)+MINUTE(A1)/60 will work if formatted as General
--
Gary''s Student


"Brent E" wrote:

Good morning,

Is there a formula or easy way to automatically convert a custom h:mm format
to a decimal format?

Situation:
I am working on putting together an Excel Template for my boss for our
departmental time sheets. I currently have these basic categories setup
(worded differently but will use descriptive terms in this example).

Day AM TIME IN, AM TIME OUT, PM TIME IN, PM TIME OUT, Total (h:mm
format), and then a Total column (Decimal format)

Currently I have the AM, PM cells and Total h:mm cells formatted as "custom"
and set to "h:mm". And the Total Decimal Column format is set to "Number" and
to 2 decimal places. Reason for 2 total columns is to sum the h:mm values to
get a total value, and payroll needs the values in a decimal format (e.g.
8.25 hours).
Suggestions would be greatly appreciated.

Cordially,