Tech Support Guy banner
Status
Not open for further replies.

Excel Date Formula to determine active months within year

1K views 11 replies 2 participants last post by  Gevans0666 
#1 ·
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

#2 ·
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
 
#11 ·
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?
 
#12 ·
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.
Top