psycoperl
Active Member
- Joined
- Oct 23, 2007
- Messages
- 341
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
- Web
I am working in an excel file that I want to highlight the current period row where Column B is the Begin of Period Date and Column C is the End of Period Date. Using the formula =AND(TODAY()>=$B3,TODAY()<=$C3) in conditional formatting. However for today (March 8, 2025) it is still highlighting the row for the period February 20, 2025 to March 5, 2025. Where am I going wrong?
| Calculator Backpay Retro Owed - General.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 42 | 9/5/24 | 8/22/24 | 9/4/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 43 | 9/19/24 | 9/5/24 | 9/18/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 44 | 10/3/24 | 9/19/24 | 10/2/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 45 | 10/17/24 | 10/3/24 | 10/16/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 46 | 10/31/24 | 10/17/24 | 10/30/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 47 | 11/14/24 | 10/31/24 | 11/13/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 48 | 11/28/24 | 11/14/24 | 11/27/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 49 | 12/12/24 | 11/28/24 | 12/11/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 50 | 12/26/24 | 12/12/24 | 12/25/24 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 51 | 1/9/25 | 12/26/24 | 1/8/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 52 | 1/23/25 | 1/9/25 | 1/22/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 53 | 2/6/25 | 1/23/25 | 2/5/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 54 | 2/20/25 | 2/6/25 | 2/19/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 55 | 3/6/25 | 2/20/25 | 3/5/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 56 | 3/20/25 | 3/6/25 | 3/19/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 57 | 4/3/25 | 3/20/25 | 4/2/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 58 | 4/17/25 | 4/3/25 | 4/16/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 59 | 5/1/25 | 4/17/25 | 4/30/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 60 | 5/15/25 | 5/1/25 | 5/14/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 61 | 5/29/25 | 5/15/25 | 5/28/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 62 | 6/12/25 | 5/29/25 | 6/11/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 63 | 6/26/25 | 6/12/25 | 6/25/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 64 | 7/24/25 | 7/10/25 | 7/23/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
| 65 | 8/7/25 | 7/24/25 | 8/6/25 | 14 | $ 1.00 | - | - | 14.00 | - | - | ||
NYS State Payroll | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D42:D65 | D42 | =C42-B42+1 |
| E42:E65 | E42 | =E41 |
| F42:F65 | F42 | =IF(DATE(2023,3,1)-[@[Begin Date]]<1,0,DATE(2023,3,1)-[@[Begin Date]]) |
| G42:G65 | G42 | =MAX(0,MIN([@[End Date]],(DATE(2024,4,1))-1)-MAX([@[Begin Date]],DATE(2023,3,1))+1) |
| H42:H65 | H42 | =MAX(0,MIN([@[End Date]],(DATE(2025,9,1))-1)-MAX([@[Begin Date]],DATE(2024,4,1))+1) |
| I42:I65 | I42 | =MAX(0,MIN([@[End Date]],(DATE(2026,9,1))-1)-MAX([@[Begin Date]],DATE(2025,9,1))+1) |
| J42:J65 | J42 | =MAX(0,MIN([@[End Date]],(DATE(2027,11,30))-1)-MAX([@[Begin Date]],DATE(2026,9,1))+1) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A3:R121 | Expression | =NOT($D3=14) | text | YES |
| A2:R1048576 | Expression | =TODAY()<$A3-13 | text | NO |
| A2:R1048576 | Expression | =AND(TODAY()>=$B3,TODAY()<=$C3) | text | NO |
