# Conditional formatting with dates

#### ayresm

##### New Member
Hi,

I have a spreadsheet that is tracking finances across a number of teams. The spreadsheet looks similar to the much simplified version below:
 A B C D E F G H J 1 Cost/day 1st Jan 8th Jan 15th Jan 22nd Jan Total Cost Cost To Date Estimated Remaining Cost 2 Tom £100 5 5 4 5 =SUM(C2:F2)*B2 =SUM(C2:D2)*B2 =SUM(E2:F2)*B2 3 Geoff £120 4 3.5 3 4 =SUM(C3:F3)*B3 =SUM(C3:D3)*B3 =SUM(E3:F3)*B3 4 Lucy £80 5 5 5 5 =SUM(C4:F4)*B4 =SUM(C4:D4)*B4 =SUM(E4:F4)*B4 5 Norman £50 2 3 2 4 =SUM(C5:F5)*B5 =SUM(C5:D5)*B5 =SUM(E5:F5)*B5

<tbody>
</tbody>

In the example above, the current date is some point between 15th Jan and 22nd Jan. Managing the finances on a weekly basis thus means all 'cost to date' values are pulled from columns C&D, while all estimated remaining costs are pulled from columns E&F.

I would like to create a conditional formula that checks that the cells referenced in the fomula of each cell in columns H and J is referencing the correct column in terms of date.

I'm happy to add two reference cells on a hidden sheet with the columns to be checked against to enable this, but I'm not sure what the formula would look like - or if it is even possible!

Matt

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### RonB1111

##### Well-known Member
Actually you can use the SIMIFS formula and it will be automatically "self checking".

Change the entries in C1:F1 to date format. Since today is April 5, I made in my worksheet C1 3/21/2013, D1 3/28/2013, E1 4/4/2013 and F1 4/11/2013. This would put today's date between 4/4 and 4/11.

Enter this formula in H2 and copy down:
=SUMIFS(C2:F2,C\$1:F\$1,"<"&TODAY()-7)*B2

Enter this formula in J2 and copy down:
=SUMIFS(C2:F2,C\$1:F\$1,">"&TODAY()-7)*B2

These formulas will automatically adjust the Cost to Date and Est Rem Costs. If you prefer to use a date you control rather than today's date, let's say you enter that date in cell A1, then replace the TODAY()-7 part of the formula with A\$1.

If your actual data extends beyond 4 weeks, I'd suggest moving the 3 total columns before the week columns and extend the formula ranges to as far out as your weeks might go (eg: C2:Z2, etc). This will avoid the need to adjust the formula when 4 weeks are exceeded.

#### ayresm

##### New Member
Actually you can use the SIMIFS formula and it will be automatically "self checking".

Change the entries in C1:F1 to date format. Since today is April 5, I made in my worksheet C1 3/21/2013, D1 3/28/2013, E1 4/4/2013 and F1 4/11/2013. This would put today's date between 4/4 and 4/11.

Enter this formula in H2 and copy down:
=SUMIFS(C2:F2,C\$1:F\$1,"<"&TODAY()-7)*B2

Enter this formula in J2 and copy down:
=SUMIFS(C2:F2,C\$1:F\$1,">"&TODAY()-7)*B2

These formulas will automatically adjust the Cost to Date and Est Rem Costs. If you prefer to use a date you control rather than today's date, let's say you enter that date in cell A1, then replace the TODAY()-7 part of the formula with A\$1.

If your actual data extends beyond 4 weeks, I'd suggest moving the 3 total columns before the week columns and extend the formula ranges to as far out as your weeks might go (eg: C2:Z2, etc). This will avoid the need to adjust the formula when 4 weeks are exceeded.

Thanks RonB.

That sounds like a much better solution. I'll give it a shot and confirm whether it has worked. My number of weeks is currently 24 and is relatively static. I'll play around with the s/sheet and see how things work, but I may move the columns to the left as you suggest.

Thanks again!

#### ayresm

##### New Member
Worked perfectly. I've opted for putting a date in a cell to drive the formula, but this has meant that I need to include an additional section to the formula. For anyone interested, here is the formula I have used, directly from my live spreadsheet:

=((SUMIFS(N7:AK7,N\$1:AK\$1,"<"&B\$1)+(SUMIFS(N7:AK7,N\$1:AK\$1,"="&B\$1)))*H7)

Where:
N1:AK1 are dates
N7:AK7 are number of days worked
B1 is user-specified the date
H7 is the cost per day value

Thanks again,

Matt

#### RonB1111

##### Well-known Member
You're welcome and thank you for providing feedback.

You could shorten your formula to:
=SUMIFS(N7:AK7,N\$1:AK\$1,"<="&B\$1)*H7)

Replies
1
Views
134
Replies
5
Views
243
Replies
1
Views
156
Replies
4
Views
88
Replies
6
Views
114

1,195,905
Messages
6,012,229
Members
441,683
Latest member
XLGeezer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back