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

Thread Starter
Joined
Sep 6, 2018
Messages
48
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

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
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

Thread Starter
Joined
Sep 6, 2018
Messages
48
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
Joined
Oct 2, 2003
Messages
65,454
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

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

Gevans0666

Thread Starter
Joined
Sep 6, 2018
Messages
48
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

Gevans0666

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

etaf

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

Gevans0666

Thread Starter
Joined
Sep 6, 2018
Messages
48
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

Thread Starter
Joined
Sep 6, 2018
Messages
48
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top