Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time as a minus value
I am making a spreadsheet to record employees flexible working hours. The standard week is 37 hours and sometimes people will work less than 37 hours one week and work more the following week to make up the difference. Therefore at the end of a week there could be a minus value to carry forward to the following week. I have tried to use time values but Excel will not accept time as a minus value. I could use decimal time thus 08:30 am would be 8.50 but I would rather use time format. Can anyone suggest a way round the problem. Any help/suggestions would be appreciated. -- Richardisa1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time as a minus value
Hi
Tools|options|Calculation select 1904 date system which allows negative hours Mike "Richardisa1" wrote: I am making a spreadsheet to record employees flexible working hours. The standard week is 37 hours and sometimes people will work less than 37 hours one week and work more the following week to make up the difference. Therefore at the end of a week there could be a minus value to carry forward to the following week. I have tried to use time values but Excel will not accept time as a minus value. I could use decimal time thus 08:30 am would be 8.50 but I would rather use time format. Can anyone suggest a way round the problem. Any help/suggestions would be appreciated. -- Richardisa1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time as a minus value
Sorry I forgot to mention that this will mess up existing dates which will be
4 years and 1 day out so you may wish to consider whether using the 1904 system is for you. "Richardisa1" wrote: I am making a spreadsheet to record employees flexible working hours. The standard week is 37 hours and sometimes people will work less than 37 hours one week and work more the following week to make up the difference. Therefore at the end of a week there could be a minus value to carry forward to the following week. I have tried to use time values but Excel will not accept time as a minus value. I could use decimal time thus 08:30 am would be 8.50 but I would rather use time format. Can anyone suggest a way round the problem. Any help/suggestions would be appreciated. -- Richardisa1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time as a minus value
Hi Richard
Even though the negative time won't display, it just shows ###### the negative value is still held there and can be used in subsequent calculations. Use the cell as you wish. Perhaps hide the column so the ##'s don't show. -- Regards Roger Govier "Richardisa1" wrote in message ... I am making a spreadsheet to record employees flexible working hours. The standard week is 37 hours and sometimes people will work less than 37 hours one week and work more the following week to make up the difference. Therefore at the end of a week there could be a minus value to carry forward to the following week. I have tried to use time values but Excel will not accept time as a minus value. I could use decimal time thus 08:30 am would be 8.50 but I would rather use time format. Can anyone suggest a way round the problem. Any help/suggestions would be appreciated. -- Richardisa1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time as a minus value
Hi Richard,
Just to add to Roger's solution. Instead of hiding the #######, you could use a custom format of [h]:mm;"Neg Value" or some other variant. In formatting anything before the semi colon relates to positive values and anything after the semi colon relates to negative values. It also extends into zero values and text. Take a look here for a detailed explanation. http://support.microsoft.com/default.aspx?id=264372 HTH Martin "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Richard Even though the negative time won't display, it just shows ###### the negative value is still held there and can be used in subsequent calculations. Use the cell as you wish. Perhaps hide the column so the ##'s don't show. -- Regards Roger Govier "Richardisa1" wrote in message ... I am making a spreadsheet to record employees flexible working hours. The standard week is 37 hours and sometimes people will work less than 37 hours one week and work more the following week to make up the difference. Therefore at the end of a week there could be a minus value to carry forward to the following week. I have tried to use time values but Excel will not accept time as a minus value. I could use decimal time thus 08:30 am would be 8.50 but I would rather use time format. Can anyone suggest a way round the problem. Any help/suggestions would be appreciated. -- Richardisa1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time as a minus value
If displaying the actual amount is important to you, a slightly roundabout
way would be; If x and y are the time cells Put =if(x-y<0,-1,1) in one cell. Format this with a custom format "+";"-". Put =max(x-y,y-x) in the next, formatted as time. Use the -1/1 cell to multiply the positive time difference in other calculations. Some combination of these should work. "Richardisa1" wrote: I am making a spreadsheet to record employees flexible working hours. The standard week is 37 hours and sometimes people will work less than 37 hours one week and work more the following week to make up the difference. Therefore at the end of a week there could be a minus value to carry forward to the following week. I have tried to use time values but Excel will not accept time as a minus value. I could use decimal time thus 08:30 am would be 8.50 but I would rather use time format. Can anyone suggest a way round the problem. Any help/suggestions would be appreciated. -- Richardisa1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to minus the time value | Excel Discussion (Misc queries) | |||
Minus time | Excel Discussion (Misc queries) | |||
Minus time | Excel Discussion (Misc queries) | |||
Minus time | Excel Discussion (Misc queries) | |||
Minus time | Excel Discussion (Misc queries) |