If date is in next calendar month

D

Dazzybeeguy

Board Regular
Joined
Jan 6, 2022
Messages
72
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi

I have a spreadsheet with column K showing various dates.

I want to add a formula in say A1 that calculates how many of those dates will occur in the calendar month.

Is there an easy way to do this.

Thanks
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
try this:
Mr Excel Questions 71.xlsm
ABCDEFGHIJK
1# December 2023 DatesDates
2142022-11-19
32023-12-18
42023-11-13
52022-12-07
62022-12-07
72022-12-09
82023-12-19
92022-12-20
102023-12-11
112023-11-19
122022-12-25
132023-11-13
142023-11-04
152022-11-17
162023-11-13
172023-12-07
182022-12-06
192023-11-20
202023-11-11
212022-11-04
222022-11-28
232023-12-16
242022-11-12
252023-12-07
262023-11-28
272022-11-29
282023-12-12
292023-11-27
302022-12-22
312023-12-07
322023-11-02
332023-12-12
342022-12-29
352023-12-10
362023-12-10
372022-12-09
382023-11-05
392022-11-11
402023-12-13
412023-11-21
422023-11-23
432022-12-04
442023-12-17
452022-12-19
462022-12-15
472023-12-15
482023-11-27
492023-11-13
502022-12-20
512022-11-10
dazzybeeguy
Cell Formulas
RangeFormula
A2A2=SUM((YEAR(K2:K51)=2023)*(MONTH(K2:K51)=12))





​
 
Upvote 0
awoohaw said:
try this:
Mr Excel Questions 71.xlsm
ABCDEFGHIJK
1# December 2023 DatesDates
2142022-11-19
32023-12-18
42023-11-13
52022-12-07
62022-12-07
72022-12-09
82023-12-19
92022-12-20
102023-12-11
112023-11-19
122022-12-25
132023-11-13
142023-11-04
152022-11-17
162023-11-13
172023-12-07
182022-12-06
192023-11-20
202023-11-11
212022-11-04
222022-11-28
232023-12-16
242022-11-12
252023-12-07
262023-11-28
272022-11-29
282023-12-12
292023-11-27
302022-12-22
312023-12-07
322023-11-02
332023-12-12
342022-12-29
352023-12-10
362023-12-10
372022-12-09
382023-11-05
392022-11-11
402023-12-13
412023-11-21
422023-11-23
432022-12-04
442023-12-17
452022-12-19
462022-12-15
472023-12-15
482023-11-27
492023-11-13
502022-12-20
512022-11-10
dazzybeeguy
Cell Formulas
RangeFormula
A2A2=SUM((YEAR(K2:K51)=2023)*(MONTH(K2:K51)=12))


That Doesn't seem to work.
Click to expand...

I tried =IF(AND(K2>TODAY(),K2<=TODAY()+30),"Yes","No") and then did a countif formula in another cell to count the Yes, just after a better way.
awoohaw said:
​
Click to expand...
 
Upvote 0
So, you changed your requirement.

Maybe I'm wrong but you are not getting the next calendar month, you are getting the next 30 days. Additionally, your formula will not get February or 31 day months correctly. You are also creating an unnecessary column.

But, to correct your formula to sum the next month (not calendar month):
=SUM((K2:K51>TODAY())*(K2:K51<=EDATE(TODAY(),1)))

TODAY() will recalculate your worksheet every time you press ENTER and on file open. Additionally, it is a volatile function and if you have a large workbook that references and uses that function you will slow the performance of your workbook.

-Best wishes.
 
Upvote 0
Solution
awoohaw said:
So, you changed your requirement.

Maybe I'm wrong but you are not getting the next calendar month, you are getting the next 30 days. Additionally, your formula will not get February or 31 day months correctly. You are also creating an unnecessary column.

But, to correct your formula to sum the next month (not calendar month):
=SUM((K2:K51>TODAY())*(K2:K51<=EDATE(TODAY(),1)))

TODAY() will recalculate your worksheet every time you press ENTER and on file open. Additionally, it is a volatile function and if you have a large workbook that references and uses that function you will slow the performance of your workbook.

-Best wishes.
Click to expand...
That does it, I had tried =IF(AND(Data!K2>TODAY(),Data!K2<=EDATE(TODAY(),1)),"Y","N") and dragged that formula down the used a countif formula but yours is definately what I needed. Thanks
 
Upvote 0
awoohaw said:
So, you changed your requirement.

Maybe I'm wrong but you are not getting the next calendar month, you are getting the next 30 days. Additionally, your formula will not get February or 31 day months correctly. You are also creating an unnecessary column.

But, to correct your formula to sum the next month (not calendar month):
=SUM((K2:K51>TODAY())*(K2:K51<=EDATE(TODAY(),1)))

TODAY() will recalculate your worksheet every time you press ENTER and on file open. Additionally, it is a volatile function and if you have a large workbook that references and uses that function you will slow the performance of your workbook.

-Best wishes.
Click to expand...
Thank you again. If I wanted the formula to look for dates in say the next week or fortnight how would that be written using the Edate
 
Upvote 0
For the original question I would use COUNTIFS
Excel Formula: 
=COUNTIFS(K2:K100,">"&TODAY(),K2:K100,"<="&TODAY()+30)

Dazzybeeguy said:
If I wanted the formula to look for dates in say the next week or fortnight how would that be written ..
Click to expand...
Just change the 30 at the end of my formula to 7 or 14
 
Upvote 0
Dazzybeeguy said:
Thank you again. If I wanted the formula to look for dates in say the next week or fortnight how would that be written using the Edate
Click to expand...

You're welcome, I'm happy you found a solution here.

The EDATE function only goes to the same day of each month.

Best Wishes!
 
Upvote 0
Peter_SSs said:
For the original question I would use COUNTIFS
Excel Formula: 
=COUNTIFS(K2:K100,">"&TODAY(),K2:K100,"<="&TODAY()+30)


Just change the 30 at the end of my formula to 7 or 14
Click to expand...
Thanks Ill remember that formula I'm sure I can use that.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Creating a Calendar for a Month with tasks
Replies
0
Views
158
anuradhagrewal
A
Z
  • Question
Dynamic Working and Week days remaining until end of month from a specific date in the month
Replies
3
Views
168
Peter_SSs
Peter_SSs
M
  • Question
Fantasy Calendar Creation stumbling..
Replies
2
Views
175
m26e4u
M
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
438
Legacy 352679
L
M
  • Solved
VBA Date issue
Replies
3
Views
146
dmt32
D

Forum statistics

Threads
1,215,488
Messages
6,125,092
Members
449,206
Latest member
ralemanygarcia

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top