Return number of out of date training in a month

[donkey_uk]

Hi All

sorry for the tital did not know what to call it, any way here goes

i have a training spredsheet and all the training that is out of date for say fork lift training will automaticll populate at the bottom under each month, this is working ok but i have to keep changing the date every year, see code here "ARP 2018, i need to change manuall each cell to 2021

SUM(IF($D$22:$D$99="END DATE",IF(TEXT(E22:E99,"mmm yyyy")="ARP 2018"1,0)))

I would like to refrence "ARP 2018" to the information to a cell ie cel a105 ( which will have 2021 in,

I know this may seem confusing, i have attached the spreedsheet to try and make a bit clear.

Can anyone please help

Thank you

[AliGW]

You were asked to update the title of your original thread, not open a new one! I have closed the older thread.

[donkey_uk]

Hi Sorry

Thank you for deleting old post for me

[Glenn Kennedy]

1. There were no dates in April 2018 in your raw data.

2. April abbreviates to APR, not ARP.

=SUM(IF($D$22:$D$99="END DATE",IF(TEXT(E22:E99,"mmm yyyy")="APR 2018",1,0)))

[donkey_uk]

Hi

Oops year APR not ARP lol sorry. But for the actual 2018 can that part be refrenced to cell a105 ?

[Glenn Kennedy]

Yes, of course!!

=SUM(IF($D$22:$D$99="END DATE",IF(TEXT(E22:E99,"mmm yyyy")="APR "&Date,1,0)))

The bl@@dy thing wouldn't work for me until I remembered about ARP...

Your sheet seems awfully slow!! Have your troubleshooted/troubleshot it to see why??

[donkey_uk]

HI Glenn

Thank you so much that worked, i just need to update all the other cells now with the new formula.

I have not troubledshot the file, not sure how to do this

[Glenn Kennedy]

When you update it... post it again here and PM me. I'll take a look for you. There is NO WAY that it should be so laggy. The use of an array formula is certainly overkill..

But for now,

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[donkey_uk]

HI

Thank you your so kind, will update and send it over

King regards

[donkey_uk]

Hi Glenn

Here is the updatd file

Thank you for taking a look

[Glenn Kennedy]

I made the formulas a bit shorter, more automated and more easily copied.

End date: =IF(E22="","",EDATE(E22,12*$E$20)-1)

To copy paste quickly, I selected E22 & E23 and copy/pasted down the row. I then selected E22:E99 and copy pasted across the columns.

I set up a Named Range containining th emonth names, starting from April (called months). I then called that from a formula in E105, to pull th emonth name from the Named Range, rather than from a mnaully adjusted entry:

=SUMPRODUCT(($D$22:$D$99="END DATE")*(TEXT(E$22:E$99,"mmmyyyy")=INDEX(Months,ROWS(E$105:E105))&Date))

Copy down, select column and paste into the other columns.

I then ran a speed test and realsied that your BIGGEST problem was the (almost) 1,000 TODAY() you were using. These are a disaster. EVERY time anything changes anywhere in yourfile, the 1000 TODAY() values recalculate...

So. I replaced them ALL with a reference to U1, where you MANUALLY enter the date on which you're looking at the sheet. The time taken to calculate the sheet fell 20-fold, to about 5% of its previous value. I suggest you DO NOT re-introduce all those TODAY()s.