#### ChrisCione

##### Board Regular

- Joined
- Aug 27, 2008

- Messages
- 92

- Office Version
- 365

- Platform
- Windows

Column B contains dates (dates I earned time off, essentially payday dates) and dates when I use the earned time.

Column F contains a numeric value representing the time earned; column H, the time used.

At the bottom I have current balance and a projected balance.

It looks something like this:

B | F | H |

Date | Earned (in hours) | Used (in hours) |

Jan 12, 2012 | 4 | |

Jan 14, 2012 | 2 | |

Jan 26, 2012 | 4 | |

Feb 14, 2012 | 4 | |

Feb 15, 2012 | 1 | |

and so on........ | ||

Current Balance | ||

Projected Balance |

<tbody>

</tbody>

The formula for the projected balance is this: =SUM(F4:F53)-SUM(H4:H53). It adds all the anticipated hours I will earn for the year and then subtracts all the anticipated hours I will use, showing me a projected year-end balance. Works perfectly.

The formula for the current balance essentially does the same, EXCEPT that it is supposed to only run the calculations for any values corresponding to a date that is equal to or less than today's date (essentially, excluding any future time used and/or earned). It looks like this:

=SUM(SUMIF($B$5:$B$53,"<="&TODAY(),F4:F53))-SUM(SUMIF($B$5:$B$53,"<="&TODAY(),H4:H53))

It's not quite working. On my current sheet, I have a value entered for the date June 14th, which is less than today's date. But it won't include it in the calculation. if I put something in for June 12th, it will include that value.

I am stumped.