• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Month and date calculation

fareedexcel

fareedexcel

Member
Hi,

I need to find the no. of years by subtracting the end date and the start date.

Start date - 1.10.17
End date - 03.05.18

1st Formula - when I try by using (end date - start date)+1/365, i'm getting the answer is 0.589 Months

2nd Formula - but when I'm tried using (end date - start date)/30, i'm getting as 7.133 Months which is correct.

May I know why there is difference in the answers.

My requirement is to show " True" if the answer is >= 0.6 months.
 
Hi ,

The first calculation has two mistakes , at least in the way it has been posted here :

(end date - start date)+1/365

should actually be :

(end date - start date + 1)/365

so that the division by 365 is for the overall numerator.

The result that this gives is the number of years , and not the number of months , since we are dividing by 365 , which is the number of days in a year.

In order to get the number of months , we need to multiply by 12 , in which case , the result will be :

7.07

Whether the correct result is 7.07 or 7.17 I cannot say ; you have to decide on what the correct value is.

If we go about it differently , the number of whole months is 7 , from October till April ; thereafter we have 3 days in the month of May.

These 3 days will be 3/31 of a month if we use the 31 days of May.

Narayan
 
Thanks Narayan and Hui for your solutions.

For the below dates,

Start date - 1.10.17
End date - 03.05.18

The difference should come as 0.7 months, because in my sheet I'm using IF formula, and if its more than 0.6 it should be true.

In case if I use DatedIF function, then the answer will be alphanumeric.
 
You must log in or register to reply here.
Back
Top