Replies
No one has replied to this post.
=DATEDIF({StartDate}+1, {EndDate}+1, "m") + 1
Replace {Start Date} And {EndDate} with the appropriate cell references.
Explanation - Excel DATEDIF(Date, Date, Interval) will look at the first day of the month to determine the number of months. If you add 1 day to the date - {StartDate}+1 - this will force excel into treating end of months as a new month and mid months as the same month.
The +1 at the end is to correct the fact that excel ignores the first month as it is not a 'complete' month.
Let me know if this solves your problem.
Phil
**EDIT** as perm Becky O'Brien comment below the correct formula is:
=DATEDIF(EOMONTH({start_date}, 0)+1, {End_date}+1, "m")+1
For those interested excel geeks amoung us -
Excel DATEDIF looks at complete months and not first dates as I originally assumed. The Inspired use of EOMONTH() ensures that the first month is ignored regardless of the length of days; the final +1 at the end is where it is counted back in.
Thank you, Philip.
I used the number of on programme payments in the Apps Payment report to check and I didn't always get the right result with that - in fact only about a third of the time.
However, after some experimentation I came up with the following, which is very similar. I have found this matches the ESFA number in 100% of the cases on the Apps Payment report. If you want to check that please do. Double-checking is always good.
=DATEDIF(EOMONTH({start_date}, 0)+1, {End_date}+1, "m")+1
EOMONTH finds the last date of the month.
I could not have done this without your help. I had got somewhat stuck with datedif and adding on ones, but hadn't tried adding one to both dates & the result.
Becky O'Brien, Nice one.
You are so right, double checking on more than a few test values is ALWAYS good.
I have updated my comment above in-case anyone else finds the post and tries to use the wrong formula.
Phil
Becky O'Brien
Excel formula for counting number of App on prog instalments?
Created
Does anyone have a formula that will work out the number of on programme payments using apprenticeship start & end dates?
I'm trying to build this into a form that will be used by people who do not have access to this information from the funding reports, but who need to be able to work out how much funding is remaining.
They could count themselves, or use a (huge!) ready reckoner I have created, but a formula would be best. I've tried a few things, but have found nothing (so far) that will count end of month dates.