Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
I am sure this question has been answered before but I am dizzy trying to
sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
Hi,
look into CPearson web http://www.cpearson.com/excel/overtime.htm there you will find times examples and the formulas for each calculation Hope this helps "blueriver" wrote: I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
CRM -
12:00 AM is midnight, which is 0 time. Depending on how you get your time, you might just need to test for E9=0, like this: =IF(E9=0,(E9-D9)*1440/60,"") Check for negative hours (depends on how you have the time). If you get them, then you will need to add 24 hours to the negative amount. -- Daryl S "blueriver" wrote: I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
=MOD(E9-D9,1)*24
Format cell as General "blueriver" wrote: I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
Hi Blueriver
The problem is that XL does not know that 12 AM is the next day if you put the date & time in the cell, your formula should work . "Formating as General " e.g. Time IN 2/19/2010 22:00 Time OUT 2/20/2010 00:00 HTH John "blueriver" wrote in message ... I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
I should have said that you can format as time only and it will show just the
time. HTH John "John" wrote in message ... Hi Blueriver The problem is that XL does not know that 12 AM is the next day if you put the date & time in the cell, your formula should work . "Formating as General " e.g. Time IN 2/19/2010 22:00 Time OUT 2/20/2010 00:00 HTH John "blueriver" wrote in message ... I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
THANK YOU ALL!!!!
"Teethless mama" wrote: =MOD(E9-D9,1)*24 Format cell as General "blueriver" wrote: I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
You're Welcome!
"blueriver" wrote: THANK YOU ALL!!!! "Teethless mama" wrote: =MOD(E9-D9,1)*24 Format cell as General "blueriver" wrote: I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Calculation
On Fri, 19 Feb 2010 09:53:04 -0800, blueriver wrote: I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E90,(E9-D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! -CRM http://office.microsoft.com/en-us/te...083091033.aspx or if that link fails find the task tracking log on this page. It allows for time increment selection, and it tallies the data separate from the main data entry sheet/print job sheet/page. http://office.microsoft.com/en-us/te...6-70f61ad52e0f |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
12 hour shift timesheet calculation | Excel Worksheet Functions | |||
Timesheet Calculation with Time Rounded | Excel Discussion (Misc queries) | |||
Time Calculation For A Timesheet To Include Lunch | Excel Worksheet Functions | |||
Timesheet Date Calculation | Excel Worksheet Functions | |||
Timesheet calculation | Excel Worksheet Functions |