Solved Convert a date to month to use in COUNTIFS criteria

July 15, 2016 at 10:16:32
Specs: Windows 7
I need help completing this countifs formula; I need to count the amount of sales that where registered in the current month, I cannot use a helper column because I get a new report everyday and it would not be feasible for me to add the helper column to the new report everyday.
How can I first convert the date to the month number so I can compare it against the current month number and add that as a criteria? The part that contains MONTH($S$1) refers to a cell where i am converting today's date to the month number. SalesSheet!$AQ$2:$AQ$999 this is the range where he sales registration date is.


See More: Convert a date to month to use in COUNTIFS criteria

Reply ↓  Report •

July 15, 2016 at 10:55:56
Adding a Helper Column can easily be accomplished with a macro. You just need an easy way to have that macro available all the time. I will now offer you that way...

If you are not familiar with the personal.xlsm workbook, you should be.

1 - Open a new workbook
2 - Place any commonly used macros in this workbook
3 - Save the workbook as personal.xlsm in your xlstart folder
3 - Hide the workbook
4 - Quit Excel
5 - Excel will ask you if want to save the changes to personal.xlsm (the hide)
6 - Click Save

Any workbook stored in the xlstart folder will automatically open whenever you open Excel. Since personal.xlsm is hidden, you won't even know it's there. However, any macros stored in personal.xlsm will be available for use.

I have a dozen or so icons on my Excel Quick Launch toolbar that have macros from my personal.xlsm workbook assigned to them. All I need to do is click an icon and the macro runs on the currently active workbook. This has saved me thousands of hours of manual work over the years.

I have one macro that adds a Helper Column (nothing more than a copy of an existing column of data) and also some Conditional Formatting. This helps me keep track of which pieces of data I have updated. When I change a cell in the original data column, it no longer matches the Helper Column value and turns Yellow. After I have completed all of the required updates, I click the same Icon again and the Helper Column and Conditional Formatting rules are deleted.

All you need is a macro that knows (or is told via an InputBox) where to place the Helper Column(s) and what formula(s) to fill it (them) with.

(When you want to add a new macro to personal.xlsm, just unhide it, add your macro, and hide it again. When you quit Excel, you'll be asked if you want to Save it.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Reply ↓  Report •

July 15, 2016 at 10:58:08
✔ Best Answer
Something like this should work.


or give it a range like


or using the TODAY() function for everything between July 1 and Today


The only way I can think of to use the MONTH() function is with a helper column.


Reply ↓  Report •

July 15, 2016 at 12:17:05
Thank you mmcconaghy, that worked

Thank you DerbyDad for the information on the personal.xlsm

Reply ↓  Report •

Related Solutions

Ask Question