Rolling 12 month Formula

[crafty carper]

Hi,
I'm using a spreadsheet to record accident/incident frequency data, based on a rolling 12 month cycle.

I require the current 12 months cycle (at this moment in time Feb12 to Jan13) to be highlighted, and then when we move into the next month, the next 12 months are automatically highlighted i.e. on the 1st Mar13 the 12 months highlighted would be Mar12 to Feb13 and so on.

I suppose one problem would be, that I couldn't test the formula until the end of the month.

Is there a formula available that would provide this requirement?

Regards,

Chris

[JBeaucaire]

Formula for:

End of current month: =EOMONTH(TODAY(),0)
Beginning of month a year ago: =EOMONTH(TODAY(),-12)+1

[crafty carper]

Many thanks for that...

How would I apply that formula to the attached?

Regards,

Chris

[JBeaucaire]

Apply it how? Where? A good sample sheet has a mockup of the desired results that you've highlighted where you want help converting to a formula instead of your manual entry(s).

[crafty carper]

Okay, so with the first formula, I could enter it into cells for the next
12 months, and with the second formula, I could enter it into cells
for the previous 12 months.
I suppose they would eventually catch each other up, and I would
have to amend the cells.

Secondly, can a cell have more than one formula?

Regards,
Chris

[JBeaucaire]

Technically, the formula in a cell is ONE long formula, but within that formula you could construct IF/THEN tests to choose which part of the formula to actually DO.

=IF(A1="dog", DoThisFormula, IF(A1="Cat", DoOtherFormula, DoSomethingElse))

[crafty carper]

Hi again,

Sorry to be a pain, but could you enter the identified formulas into my attached spreadsheet please. Once I see how you've entered them, I should be okay going forward.

Many thanks in anticipation,

Chris

[Ace_XL]

Use conditional formatting (CF) across your range of data, Use the belwo formula in CF

Please Login or Register  to view this content.

format -- colour

See attached (1st table). You can test the same by changing the system date to a different month on your PC/laptop. Hope this helps

[JBeaucaire]

The CF formula I would recommend is:

=AND(C$3>=EOMONTH(TODAY(),-13)+1, C$3<EOMONTH(TODAY(),-1)+1)