Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

So I am trying to find a formula for excel. Bascically I have two dates and I need to know the number of months between them. But to add complexity, I need a rule in there that if the earlier date is the 1st to 15th it will be a whole month but if it is 16th to end of the month it will be a half month.

For example: Date 1: April 4, 2012 Date 2: December 31, 2012 Answer: Number of months would be 9 months

If Date 1 was April 16, 2012, with the same Date 2 of December 31, 2012, then the number of months would equal 8.5.

Can anyone help me with this one? You help would be greatly appreciated. many thanks

share|improve this question

migrated from Jan 17 '13 at 16:04

This question came from our site for professional and enthusiast programmers.

Not a programming question. should have been asked at Super User – brettdj Jan 17 '13 at 7:24
up vote 1 down vote accepted

Assuming the dates are in G8, H8

=DATEDIF(G8, H8, "m") +1 -IF(DAY(G8) <16,0, 0.5)

enter image description here

share|improve this answer
Wow that took a few goes to get right :-) – Preet Sangha Jan 17 '13 at 3:44
Didn't work for me. I got the error #VALUE! – Chloe Duncan Jan 17 '13 at 3:57
I'm using Excel 2010. – Preet Sangha Jan 17 '13 at 3:59
So am I. Both my dates are in date format, same format but doesn't work. Any idea why? – Chloe Duncan Jan 17 '13 at 4:00
my contact details are in my profile. send me a sample excel sheet and I'll have a quick look – Preet Sangha Jan 17 '13 at 4:04

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.