Formula to check if Year & Month are the same.

howardJennings

New Member
Joined
Aug 28, 2015
Messages
11
I have a complicated spread sheet which calculated insurance premiums. part of the sheet calculated the 'billing period'
it's pretty simple
IF the 'Effective Date" is this before the invoice date billing period = number of days in the month
ELSE Billing period is Number of days in the month LESS the 'Effective Date".

I have to check whether the 'Effective Date" YEAR & MONTH are the same as the 'Billing Date' YEAR & MONTH (i'm ignoring the day). The formula I'm using to do this is

=IF(AND(YEAR(H10)=YEAR($E$3),MONTH(H10=MONTH($E$3))),"TRUE","FALSE")

Cell H10 & E3 contain the date that i need to check.

The formula works once or twice and then stops, what have i done wrong?
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

howardJennings

New Member
Joined
Aug 28, 2015
Messages
11
CELL E3 is the fixed date to reference

CELL H10 is the cell you'd enter the "effective Date' in

the first time i enter a date in H10 the formula calculates correctly and displys either 'TRUE" or "FALSE"

however it's hit or miss whether it will calculate correctly again if i change the date in H10
 

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
If you want real TRUEs and FALSEs instead of strings "TRUE" and "FALSE", you can shorten your fomula to
Code:
=AND(YEAR(H10)=YEAR($E$3),MONTH(H10)=MONTH($E$3))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

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
Top