Convert time entered to decimal

[smartgal]

I am trying to create a spreadsheet in which I can enter an actual time (1:13
pm) and have it convert it to a decimal. Tried excel help, to no avail.
Output of this is I want an employee to enter their start and end times each
day and have the sheet calculate the time spent at work. Thanks!

 

[T. Valko]

Just subtract the start time from the end time.

A1 = start time = 1:13 PM
B1 = end time = 9:27 PM

=B1-A1

Format as [h]:mm

Result = 8:14

If the times might span past midnight then use this version:

A1 = start time = 11:13 PM
B1 = 7:27 AM

=MOD(B1-A1,1)

Format as [h]:mm

Result = 8:14

 

[Pecoflyer]

To convert time to decimal multiply by 24 and format as General

 

[Sheeloo]

If your time is in A1 use this in A2 to get what you want
=A1*24

 

[smartgal]

Have tried all, to no avail. Here's a sample of my data (and I've also done
this formatted as time, or 8:19 / 12:17, etc). I've multiplied by 24,
formatted as h:mm, formatted as decimal - I've tried it all.

What I want is for my person to enter their *actual* times and have my sheet
convert it to the proper decimal time as our time clock does so that she gets
a running total of her time each day and through the week. Sample below.

Start time Lunch out Lunch in End time Subtotal Lunch total Day total
8.19 12.17 12.51 5.05
8.58 12.02 1.27 4.02

Just subtract the start time from the end time.

A1 = start time = 1:13 PM
B1 = end time = 9:27 PM

=B1-A1

Format as [h]:mm

Result = 8:14

If the times might span past midnight then use this version:

A1 = start time = 11:13 PM
B1 = 7:27 AM

=MOD(B1-A1,1)

Format as [h]:mm

Result = 8:14


--
Biff
Microsoft Excel MVP

I am trying to create a spreadsheet in which I can enter an actual time
(1:13
pm) and have it convert it to a decimal. Tried excel help, to no avail.
Output of this is I want an employee to enter their start and end times
each
day and have the sheet calculate the time spent at work. Thanks!

 

[Sheeloo]

If the user enters 8.19 in A1
then you can get the time with this in B1
=TIME(INT(A1),(A1-INT(A1))*100,0)

Have tried all, to no avail. Here's a sample of my data (and I've also done
this formatted as time, or 8:19 / 12:17, etc). I've multiplied by 24,
formatted as h:mm, formatted as decimal - I've tried it all.

What I want is for my person to enter their *actual* times and have my sheet
convert it to the proper decimal time as our time clock does so that she gets
a running total of her time each day and through the week. Sample below.

Start time Lunch out Lunch in End time Subtotal Lunch total Day total
8.19 12.17 12.51 5.05
8.58 12.02 1.27 4.02

Just subtract the start time from the end time.

A1 = start time = 1:13 PM
B1 = end time = 9:27 PM

=B1-A1

Format as [h]:mm

Result = 8:14

If the times might span past midnight then use this version:

A1 = start time = 11:13 PM
B1 = 7:27 AM

=MOD(B1-A1,1)

Format as [h]:mm

Result = 8:14


--
Biff
Microsoft Excel MVP

I am trying to create a spreadsheet in which I can enter an actual time
(1:13
pm) and have it convert it to a decimal. Tried excel help, to no avail.
Output of this is I want an employee to enter their start and end times
each
day and have the sheet calculate the time spent at work. Thanks!

 

[smartgal]

Okay, let me just say what I'm trying to get to:

start time: 8:58 am
out for lunch: 12:02 pm
back from lunch: 1:27 pm
end time: 4:02 pm

Our time clock calculates the day's total as 5.6 hours. I need to be able
to do the same.

 

[David Biddulph]

=((B1-A1)+(D1-C1))*24 (formatted as general or number, not as time) gives
5.65 hours
(assuming that your 4 times are in A1 to D1 respectively).

 

[Fred Smith]

Assume your times are in a1:d1. Use the following formula:

=((b1-a1)+(d1-c1))*24
Format as general

Regards,
Fred.