Calculating Time with Date/Time checking in formula

[cmatera]

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

 

[Roger Govier]

Hi

Maybe
=if(month(date_cell)=month(previous_date),max(balance,0)+10,balance)

where date_cell and previous_date are the cell references holding your
dates.

 

[cmatera]

Hi

Maybe
=if(month(date_cell)=month(previous_date),max(balance,0)+10,balance)

where date_cell and previous_date are the cell references holding your
dates.

Thanks - I will give it a try and let you know how it turns out!