# Excel Date Formula to determine active months within year

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### Gevans0666

Attached is a excel sheet where I've made 8 date calculations...

Te issue is in column E =IF(I2="",0,MIN(12,(DAYS360(H2,\$O\$1)/30-D2)))

Some contracts start in 2019 while others end in 2019... I am trying to count which months contract will be effective... what am I missing? Do I need an additional column or can this be accomplished in one formula?

Thanks

#### Attachments

• 29 KB Views: 3

#### etaf

Wayne
Moderator
can you give some examples where the formula is wrong and what answer you need
for example
row 15 is 9 months
because it starts on 31st March - did you want to count 10 months?

there is also
=DATEDIF(start_date,end_date,"m")
=DATEDIF(H2,\$O\$1,"m")
will give you the number of months between dates - rounds down

but row 73 - only complete months

if you want to nearest month
=MIN(12,DATEDIF(H2,\$O\$1+15,"m")) rounds up

see here
https://exceljet.net/formula/get-months-between-dates

#### Gevans0666

For example:

Line 33 should read 4 months (starts in March and ends in June of 2019)

Line 40 should read 9 months (effective for first 9 months of 2019)

#### etaf

Wayne
Moderator
why are using the end date as 31/12/19 in cell O2
and not the column K?

=IF(OR(I2="",K2<DATEVALUE("1/1/19")),0,MIN(12,DATEDIF(H2,K2+15,"m")-D2))

#### Gevans0666

Oh wow... I NEVER would have figured that formula out! Works like a charm... thanks

Wayne
Moderator
you are welcome

#### Gevans0666

Okay - so having issue with this... it is incorrectly calculating those records that start partway through 2019 and run past 2019 (could be 202 up to 2029)... to a max of 12 for the current year.

#### Attachments

• 20.8 KB Views: 1

#### Gevans0666

....same with those records that start prior to 2019 and end partway in 2019

#### etaf

Wayne
Moderator
can you give some row numbers and what you want to see
I started looking into it and was a little confused

Here it is....

#### Attachments

• 59.9 KB Views: 3

#### Gevans0666

I was wondering if you could have a look at the formula I am using in M3... it's a calculation to spread an amount evenly over 12 months based on a date range -- while it works - it is always 1 month ahead.. meaning if the project starts in Oct the savings show up in Nov (but I want them actually showing in Oct)... what am I missing here?

#### Gevans0666

In case I didn't explain it correctly -- I am trying to determine the 'active' months within a date range (specifically 01 Jan 2019 to 31 Dec 2019). Some records start prior to Jan 01 and end in 2019... some don't tart until midway through 2019 and end in 2022 or further.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On