Solved IF Function depending on date

[Kayleigh]

Hi,
I know that this is mainly an Access-based forum, however I have a quick question related to Excel.

I am trying to write a formula which averages various cells depending on today's date.
e.g. If today before 10 March include cell B4,
else if today before 10 June include cells B4 and L4
else include cells B4, L4 and S4.

Can anyone suggest how to write this function - when I tried I got a volatile error?!

 

[Uncle Gizmo]

Are there only 3 Cells - B4, L4 and S4?

 

[Kayleigh]

To average - yes. But formula will be replicated down a column.

 

[Isaac]

Hi,
I know that this is mainly an Access-based forum, however I have a quick question related to Excel.

I am trying to write a formula which averages various cells depending on today's date.
e.g. If today before 10 March include cell B4,
else if today before 10 June include cells B4 and L4
else include cells B4, L4 and S4.

Can anyone suggest how to write this function - when I tried I got a volatile error?!

What did you try?

Are you using any other custom functions (written in VBA, used on a workskheet) on the worksheet?

 

[Kayleigh]

A simple formula on worksheet:

=IFS(TODAY()>=12/7/2021,AVERAGE(FY8,KQ8,RD8), TODAY()>=8/4/2021,AVERAGE(FY8,KQ8), TODAY()>=31/8/2020, FY8)

Actually didn't encounter error this time but did not calculate correctly. What can I change?

 

[Isaac]

I'd say try an IF formula - not ifs.

=if(date condition,average,if(datecondition,average,if(datecondition,average)))

 

[Kayleigh]

Thanks for suggesting.

Tried this and worked first time!

=IF(TODAY()>=12/7/2021, AVERAGE(FY14,KQ14,RD14), (IF(TODAY()>=8/4/2021, AVERAGE(FY14, KQ14), FY14)))

 

[Isaac]

Your original post mentioned three conditions. Was that accurate?

 

[Isaac]

Thanks for suggesting.

Tried this and worked first time!

=IF(TODAY()>=12/7/2021, AVERAGE(FY14,KQ14,RD14), (IF(TODAY()>=8/4/2021, AVERAGE(FY14, KQ14), FY14)))

awesome!

 

[Kayleigh]

Yes but I'm assuming if first two are not TRUE, third would be.