# Excel Date Formula to determine active months within year

Discussion in 'Business Applications' started by Gevans0666, Jan 13, 2019.

Not open for further replies.

Joined:
Sep 6, 2018
Messages:
40
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

File size:
29 KB
Views:
3
2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
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

Joined:
Sep 6, 2018
Messages:
40
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)

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
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))

Joined:
Sep 6, 2018
Messages:
40
Oh wow... I NEVER would have figured that formula out! Works like a charm... thanks

6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
you are welcome

Joined:
Sep 6, 2018
Messages:
40
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.

#### Attached Files:

• ###### Capture.JPG
File size:
20.8 KB
Views:
1

Joined:
Sep 6, 2018
Messages:
40
....same with those records that start prior to 2019 and end partway in 2019

9. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
can you give some row numbers and what you want to see
I started looking into it and was a little confused

Joined:
Sep 6, 2018
Messages:
40
Here it is....

#### Attached Files:

• ###### Date Calculations.xlsx
File size:
59.9 KB
Views:
3

Joined:
Sep 6, 2018
Messages:
40
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?

Joined:
Sep 6, 2018
Messages:
40
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.

As Seen On