Conditional formatting with dates

[ayresm]

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!

Thanks in advance!

Matt

 

[RonB1111]

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]

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]

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]

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)