Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting time
Hi,
I am creating a spreadsheet to track the number of hours I work in a given day then adding them up for the month. Column A is my regular hours, column B I type in the actual hours I came in and left that day, column C is the difference between my regular 8 hour shift and the actual time I worked. I get into a problem when I get into negative values. For example I am suppose to work 8 hours a day but if I leave early or come in late by one hour I want it to show -1 under column C. The next day I will come in one hour early and stay one hour late. So for the next day column C will show 2. At the end of the month my net hours over/under will be 1. Regual Actual Time Over/Under 8:30 AM 4:30 PM Total Over/Under = xxxx I can't get the negative values to show right. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting time
If you change to the 1904 date system (toolsOptionsCalculation, check the
1904 date system checkbox), then negative time shows correctly. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ivano" wrote in message ... Hi, I am creating a spreadsheet to track the number of hours I work in a given day then adding them up for the month. Column A is my regular hours, column B I type in the actual hours I came in and left that day, column C is the difference between my regular 8 hour shift and the actual time I worked. I get into a problem when I get into negative values. For example I am suppose to work 8 hours a day but if I leave early or come in late by one hour I want it to show -1 under column C. The next day I will come in one hour early and stay one hour late. So for the next day column C will show 2. At the end of the month my net hours over/under will be 1. Regual Actual Time Over/Under 8:30 AM 4:30 PM Total Over/Under = xxxx I can't get the negative values to show right. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting time
that worked, thanks Bob.
Now that i have made this global excel change which affects every spread sheet I work on if i have to work on other spreadsheets which happen to have date/time would it affect them and change the results possibly giving erroneous results? "Bob Phillips" wrote: If you change to the 1904 date system (toolsOptionsCalculation, check the 1904 date system checkbox), then negative time shows correctly. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ivano" wrote in message ... Hi, I am creating a spreadsheet to track the number of hours I work in a given day then adding them up for the month. Column A is my regular hours, column B I type in the actual hours I came in and left that day, column C is the difference between my regular 8 hour shift and the actual time I worked. I get into a problem when I get into negative values. For example I am suppose to work 8 hours a day but if I leave early or come in late by one hour I want it to show -1 under column C. The next day I will come in one hour early and stay one hour late. So for the next day column C will show 2. At the end of the month my net hours over/under will be 1. Regual Actual Time Over/Under 8:30 AM 4:30 PM Total Over/Under = xxxx I can't get the negative values to show right. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting time
A2: Time In
B2: Time Out C2: Actual time =(B2-A2)*24 D2: Time Over/Under =C2-8 No need to change to 1904 date system "Ivano" wrote: Hi, I am creating a spreadsheet to track the number of hours I work in a given day then adding them up for the month. Column A is my regular hours, column B I type in the actual hours I came in and left that day, column C is the difference between my regular 8 hour shift and the actual time I worked. I get into a problem when I get into negative values. For example I am suppose to work 8 hours a day but if I leave early or come in late by one hour I want it to show -1 under column C. The next day I will come in one hour early and stay one hour late. So for the next day column C will show 2. At the end of the month my net hours over/under will be 1. Regual Actual Time Over/Under 8:30 AM 4:30 PM Total Over/Under = xxxx I can't get the negative values to show right. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting time
The underlying date number is 4 years later, so any existing dates will show
4 years on. But it is workbook specific, not Excel wide, so doesn't affect existing workbooks. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ivano" wrote in message ... that worked, thanks Bob. Now that i have made this global excel change which affects every spread sheet I work on if i have to work on other spreadsheets which happen to have date/time would it affect them and change the results possibly giving erroneous results? "Bob Phillips" wrote: If you change to the 1904 date system (toolsOptionsCalculation, check the 1904 date system checkbox), then negative time shows correctly. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ivano" wrote in message ... Hi, I am creating a spreadsheet to track the number of hours I work in a given day then adding them up for the month. Column A is my regular hours, column B I type in the actual hours I came in and left that day, column C is the difference between my regular 8 hour shift and the actual time I worked. I get into a problem when I get into negative values. For example I am suppose to work 8 hours a day but if I leave early or come in late by one hour I want it to show -1 under column C. The next day I will come in one hour early and stay one hour late. So for the next day column C will show 2. At the end of the month my net hours over/under will be 1. Regual Actual Time Over/Under 8:30 AM 4:30 PM Total Over/Under = xxxx I can't get the negative values to show right. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting Time can it be done with a fx? | Excel Discussion (Misc queries) | |||
subtracting time | Excel Worksheet Functions | |||
subtracting time | Excel Worksheet Functions | |||
Subtracting Time | Excel Discussion (Misc queries) | |||
Subtracting Time from NOW() Value | Excel Worksheet Functions |