Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Time with Date/Time checking in formula
I have a spreadsheet where I need to calculate the time off that
people have acquired throughout the pay period. People ET or Earn Time at a set rate at the beginning of each month. Also people have an ET begining balance which is also set at the begining of each month AND may be zero. So for example Joe might earn time at a right of 10.0 hours each month. However he might have an ET balance of 0 currently. Here's how the spreadsheet is currently setup: Each day has 3 columns for example: 7/28/08 ET USED BALANCE - 2 -3 This example assumes that Joe has a 0 ET beginning balance and has used two hours of time thus giving him a negative for the day. If on 7/29/08 Joe uses an additional two hours we'd want to have that added to the last balance to give us -4 etc Here's where the problem comes in. If on 7/31/08 the Balance is Negative for example 7/31/08 ET USED BALANCE - 0 -3 Then on 8/1/08 When the New ET Rate (10) kicks in here's what happens ET USED BALANCE 10 0 7 OR -3 +10-0 = 7 The result should be 10 since it is a NEW month. If Joe had a positive balance it should be 3+10-0=13 Does anyone know how to do this via formula? Thanks Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Time with Date/Time checking in formula
Hi
Maybe =if(month(date_cell)=month(previous_date),max(bala nce,0)+10,balance) where date_cell and previous_date are the cell references holding your dates. -- Regards Roger Govier "cmatera" wrote in message ... I have a spreadsheet where I need to calculate the time off that people have acquired throughout the pay period. People ET or Earn Time at a set rate at the beginning of each month. Also people have an ET begining balance which is also set at the begining of each month AND may be zero. So for example Joe might earn time at a right of 10.0 hours each month. However he might have an ET balance of 0 currently. Here's how the spreadsheet is currently setup: Each day has 3 columns for example: 7/28/08 ET USED BALANCE - 2 -3 This example assumes that Joe has a 0 ET beginning balance and has used two hours of time thus giving him a negative for the day. If on 7/29/08 Joe uses an additional two hours we'd want to have that added to the last balance to give us -4 etc Here's where the problem comes in. If on 7/31/08 the Balance is Negative for example 7/31/08 ET USED BALANCE - 0 -3 Then on 8/1/08 When the New ET Rate (10) kicks in here's what happens ET USED BALANCE 10 0 7 OR -3 +10-0 = 7 The result should be 10 since it is a NEW month. If Joe had a positive balance it should be 3+10-0=13 Does anyone know how to do this via formula? Thanks Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Time with Date/Time checking in formula
On Aug 11, 8:28*am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: Hi Maybe =if(month(date_cell)=month(previous_date),max(bala nce,0)+10,balance) where date_cell and previous_date are the cell references holding your dates. -- Regards Roger Govier "cmatera" wrote in message ... I have a spreadsheet where I need to calculate the time off that people have acquired throughout the pay period. People ET or Earn Time at a set rate at the beginning of each month. *Also people have an ET begining balance which is also set at the begining of each month AND may be zero. So for example Joe might earn time at a right of 10.0 hours each month. *However he might have an ET balance of 0 currently. Here's how the spreadsheet is currently setup: Each day has 3 columns for example: 7/28/08 ET * *USED * *BALANCE - * * * *2 * * * * * *-3 This example assumes that Joe has a 0 ET beginning balance and has used two hours of time thus giving him a negative for the day. If on 7/29/08 Joe uses an additional two hours we'd want to have that added to the last balance to give us -4 etc Here's where the problem comes in. *If on 7/31/08 the Balance is Negative for example 7/31/08 ET * * USED * *BALANCE - * * * * 0 * * * * * * -3 Then on 8/1/08 When the New ET Rate (10) kicks in here's what happens ET * * USED * * BALANCE 10 * * *0 * * * * * * 7 OR -3 +10-0 = 7 * The result should be 10 since it is a NEW month. If Joe had a positive balance it should be 3+10-0=13 Does anyone know how to do this via formula? Thanks Chris Thanks - I will give it a try and let you know how it turns out! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating the time between one date and another | Excel Discussion (Misc queries) | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
calculating date/time | Excel Discussion (Misc queries) | |||
Calculating date/time | Excel Discussion (Misc queries) | |||
Calculating effective time from start/end date+time | Excel Worksheet Functions |