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?
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
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.
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?
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
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!