Thank you for writing back and I am sorry for this late reply. I am living in a different time zone and by the time of this writing it is 12:15PM in my country. My last answer to you was 4AM yesterday
I gave you 2 formulas. One for the calculation of the 6th-month visit date and the other is the counting of the SUP visits.
Here are the explanation:Calculating 6th-month visit dates
In that formula, the actual computation of the 6th-month date from the Start Date is this part:
That formula will ADD 6 to the month found in column C. However, to clean up your worksheet, I added a TEST formula to see if the Start Date is BLANK. Here is that TEST formula in bold
The formula means:
IF C4 (the cell containing Start Date) is BLANK, ("" means blank in Excel), the cell (the cell which is supposed to show the 6th-month date) will show BLANK (or "").
IF C4 is NOT BLANK, then the cell will display the result of the formula DATE(YEAR(C4),MONTH(C4)+6,(DAY(C4))).
The IF function in Excel has this format:
IF(logical test that results to True or False
, Value if True, Value if False).Counting the SUP Visits
In that formula above, again, I added a TEST formula to cleanup the results to be displayed. The actual formula that does the counting of SUP Visits is this:COUNTIF(E4:J4,"<="&TODAY())
That formula will COUNT the cells in the range E4 through J4, which correspond to the 6 visits. However, the cells are only COUNTed when the date in the cell is less than or equal to today's date. Since the formula is using the volatile TODAY() function, each time you close your file, it will always prompt you to Save or Not even though you did not change any thing in the worksheet. The reason for this is because the TODAY function takes a dynamic date so Excel takes this new and continually new date as a change in the file causing it to prompt to save your changes.
The TEST or cleanup part that I added was in bold:
It may appear tricky because of he AND function. Remember that IF tests a logical expression that results to True or False. Then the next argument is Value IF True, and the last argument is Value IF False.
Here is a sample:
=IF(A1 > 70, "Pass", "Fail")
In that formula, a grade is tested if it passed or failed. If A1 has 64, since it is NOT > 70, it is false so Fail will be shown.
In the TEST formula that I built for you, the logical expression tested is the AND function there since AND function will result to True or False. The AND means ALL tests must be True to result to True. Likewise, all tests must be false to return False.
Here is the AND part:AND(E4="",F4="",G4="",H4="",I4="",J4="")
Remember that "" means blank. If E4, G4, H4, I4, and J4 are all blank cells, the AND expression results to True. If any of the cells E4, G4, H4, I4, and J4 has a content, the AND will give False.
So IF all cells are blank, the SUP Visit value must be blank ( or "" in Excel expression). Otherwise, show the results of the formula COUNTIF(E4:J4,"<="&TODAY()).
Please remember to rate my service positively (3-5 stars/faces)
if this helped. Tips
are always highly appreciated!
If you have any other questions, please ask me or reply to me
– I’ll be happy to respond.