I needed to create a macro in order to execute a task based on if there will be new data for the previous month. In this article, I will just explain how I check for new data. Additionally, I will show you how to convert a month name into a date in order to have the last update.

 

When I use the formula ?

To check if there are new values for the previous month.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How are the formula ?

=LOOKUP()

=COUNTIF()

=IF()

=MONTH()

=DATEVALUE()

=DAY()

=YEAR()

=NOW()

I will add 3 formulas, one in the cell C4, one in D4 and one in D5:

• C4 -> =LOOKUP(2,1/(A:A<>""),A:A)
• D4 -> =COUNTIF(A:A, G3)
• D5 -> =IF(COUNTIF(A:A, C2)=D2,"no","yes")

After that, I can add my new data. Before to save and close the file, I will copy the cell C4 and D4 and paste them as values in the cell C2.

To get the last update, first I will have to convert the month name into a number so my formula in E4:

• =MONTH(DATEVALUE(C4&"1"))

Now the formula in F4 to get the date of the last update:

• =DAY(NOW())&"/"&E4&"/"&YEAR(NOW())