Formula to count months

Glacombe

Board Regular
Joined
Oct 31, 2018
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
Hello
I have the following formula that counts how many months worked in a fiscal year and not counting holidays and also if worked 10 or more days in the month it will count that month. The formula verifies the start date which is in AJ10 and the end date which is in AK10.

=IF(AND(ISBLANK(AJ10),ISBLANK(AK10)),0,(YEAR(AK10)-YEAR(AJ10))*12+MONTH(AK10)-MONTH(AJ10)-1+IF(NETWORKDAYS.INTL(AJ10,EOMONTH(AJ10,0))>=10,1,0)+IF(NETWORKDAYS.INTL(EOMONTH(AK10,-1),AK10,1)>=10,1,0))

What I am trying to revise this formula is to count if worked half the month to count it. For example if the period would be April 1, 2020 to September 15, 2020, I would like the result of the formula to show 5.5 months instead 6.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,243
Office Version
  1. 365
Platform
  1. Windows
Currently you're defining a month as 10 working days in any calendar month. So a period as short as 17 April to 14 September 2020 counts as two months.

If you make the change, how will you define a month? Half a month?
 

Glacombe

Board Regular
Joined
Oct 31, 2018
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
The result of my formula counts if equals or more than 10 working days, excluding weekend, it counts as a full month and the result of the above start date and end date is 6 months. As the person worked 10 work days in April, therefore counts the full month and that person worked more than 10 work days in September, therefore, counts the full month of September.
I would like to revise that formula to count only half of the month if working 14 days in that month, therefore the above start and end date would be 5.5 months.
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
289
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I left this in many cells to make it easier to follow. You can smash it all into one cell if you please.
MrExcelPlayground.xlsm
IJ
104/20/20211/22/2022
11
12Full month > X days14
13half month >= X days5
14no month < X days5
15
16whole months8
17Start days9
18End days15
19start months0.5
20end months1
21Total Months9.5
Sheet16
Cell Formulas
RangeFormula
J16J16=(YEAR(J10)*12+MONTH(J10))-(YEAR(I10)*12+MONTH(I10))-1
J17J17=NETWORKDAYS.INTL(I10,EOMONTH(I10,0),1)
J18J18=NETWORKDAYS.INTL(DATE(YEAR(J10),MONTH(J10),1),J10,1)
J19J19=IF(J17>J12,1,IF(J17>=J13,0.5,0))
J20J20=IF(J18>J12,1,IF(J18>=J13,0.5,0))
J21J21=J16+J19+J20


I assumed that if you work less than 5 days, it's 0 months. If it's more than 14 it's one month, between it's half. You can change that as you please.
 

Glacombe

Board Regular
Joined
Oct 31, 2018
Messages
101
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Sorry my start date and end date are on different columns and would like to count half the month if working 14 days or more
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
289
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
How about:
MrExcelPlayground.xlsm
AJAKAL
104/20/20211/22/20228.5
Sheet16
Cell Formulas
RangeFormula
AL10AL10=IF(OR(AJ10="",AK10=""),"",((YEAR(AK10)*12+MONTH(AK10))-(YEAR(AJ10)*12+MONTH(AJ10))-1)+IF(NETWORKDAYS.INTL(AJ10,EOMONTH(AJ10,0),1)>=14,0.5,0)+IF(NETWORKDAYS.INTL(DATE(YEAR(AK10),MONTH(AK10),1),AK10,1)>=14,0.5,0))
 

Glacombe

Board Regular
Joined
Oct 31, 2018
Messages
101
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Yes, it worked thank you very much James :)
 

Glacombe

Board Regular
Joined
Oct 31, 2018
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
Sorry James I was too quick it worked for the dates above but when I have the start date April 1, 2020 and end date December 1, 2020, the result shows 7.5 when it should be 8 months
 

Glacombe

Board Regular
Joined
Oct 31, 2018
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
I also have the formula looking if there are not dates in either column to leave the cell blank
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
289
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
What the formula is doing is counting the full months between the dates first, so April through December counts May, June, July,..November (7 months), then adds 0 or .5 months for the edge months. That's why I had >14 is a month, between 5 and 14 is half month, less than 5 was 0 months. I could make it > 19 workdays is a month, >=14 is half, less than 14 is 0. For example, what if it was April 2- Dec 1? Would that be a month? What would make a full month?
 

Forum statistics

Threads
1,140,999
Messages
5,703,645
Members
421,307
Latest member
morrden86

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