Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…
I have a workbook that contains 2 sheets. The first sheet is my results page and contains vlookup formulas the second sheet is where my data is stored.
On my first sheet I have 3 columns one is headed 'type' the next is headed 'complete' and the last one is headed 'refresher due?'.
The vlookup refers to my data sheet and populates my 2nd column which in turn triggers my 3rd column if applicable.
Under type I have various types of traing listed as text. I have used 'countblank to determine the number of training courses that have not been completed and used vlookup to populate a cell within sheet 1.
If the 2nd column is populated with a date and triggers a refresher date some times the refresher date has expired e.g. completed 01.03.06 refresher due 01.03.07. I have used conditional formatting to highlight cell, but is there anyway I could count the number of cells that are expired. I was thinking of creating additional columns in my 'data' sheet and referencing that way, but only way I think that would work would be to highlight the cells and maybe count them that way.
I also have training that is due to expire ( 3 months) for employees would like to count that too.
My 1st sheet 'results' has a list of legends indicating what the coloured formatting refers too.
I put your formula into my sheet-
The above has counted all cells with a date prior to todays date. As I stated I have used the countblank formula to establish training that hasn't been completed. If the cell has a date then the training has been completed, but may have expired. I'm trying to find a way of counting the expired too and maybe adding onto the non-completed total.
"The above has counted all cells with a date prior to todays date."
Yes, well it should. It was just an example. You would need to replace "08/08/2007" with the date you are interested in or a cell reference (without quotes).
Unfortunately, the dates will differ from each other so I can't define a certain date.
Would the best option be to insert additional columns next to dates within my data sheet and use conditional formatting to highlight the dates when they expire. If I done that I would then need to count the highlight cells and add them to my total.
I'm a bit confused here - can't you use the conditions as in your conditional format.
At the moment I have populated 5 cells with the count of training (type) not completed. The only way I think I can count the expired training is to count the highlighted cells then add to the 'coutblank (not completed)' formula.
e.g. col a has 50 blank cells, the next col has the refresher dates 10 of them are expired. Want to be able to populate a cell on the first sheet with the total outstanding reading 60 instead of 50.
Hi, don't think I have explained this correctly.
Column a titled completed (range a2:a142
Column b titled refresher due (range b2:b142)
if cell 'a2' is populated with this date (01/06/07) it will populate cell 'b2' with this date (01/09/07) Already have a formula in place to calculate refresher date.
I want to populate a cell within the sheet that will tell me how many dates are due to expire within 3 months based on the current date of the sheet opening.
I tried using this formula, but can't get it to work-
I'm still a little confused.
I have created a list of dates in column A. Using column B as a helper column, in B1 I have used
and copied down. Column B needs to be formatted as number.
In another cell I have
which correctly counts the number of dates in A that are less than 3 months away (give or take a day or two).
Thanks for the above. Done what you said but, it seems to be counting all the negative numbers. Was only wanting it to count if cell is populated with 0-89 days left, hope that makes sense.
please note, the -- are consecutive minus signs.
This thread is now locked and can not be replied to.