Convert time entered to decimal

S

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

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
 
S

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


T. Valko said:
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


smartgal said:
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!
 
S

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)

smartgal said:
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


T. Valko said:
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


smartgal said:
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!
 
S

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.
 
D

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).
 
F

Fred Smith

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

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

Regards,
Fred.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top