Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
To convert time to decimal multiply by 24 and format as General -- Pecoflyer Cheers ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=30648 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
If your time is in A1 use this in A2 to get what you want
=A1*24 "smartgal" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
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" wrote: 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" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
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" wrote: 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" wrote: 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" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
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. "Sheeloo" wrote: If your time is in A1 use this in A2 to get what you want =A1*24 "smartgal" wrote: 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
=((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). -- David Biddulph "smartgal" wrote in message ... 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. "Sheeloo" wrote: If your time is in A1 use this in A2 to get what you want =A1*24 "smartgal" wrote: 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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert time entered to decimal
Assume your times are in a1:d1. Use the following formula:
=((b1-a1)+(d1-c1))*24 Format as general Regards, Fred. "smartgal" wrote in message ... 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. "Sheeloo" wrote: If your time is in A1 use this in A2 to get what you want =A1*24 "smartgal" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert time into decimal | New Users to Excel | |||
convert time into decimal | New Users to Excel | |||
convert decimal to time | Excel Discussion (Misc queries) | |||
How to convert time to decimal | New Users to Excel | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) |