Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com 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
add comment

1 Answer

up vote 0 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
show 3 more comments

Your Answer

 
discard

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