1

Hello I have two dates, the initial one is in cell A1 (01/04/2024) and the final one in cell A2 (13/12/2024), I have looked for a formula that allows me to find the approximate number (A4) and the exact number (A5) of months between two dates.

A4=(YEAR(A2)-YEAR(A1))*12+(MONTH(A2)-MONTH(A1))+((DAY(A2)-DAY(A1)))/30

A4=YEARFRAC(A1,A2)

A5=(YEAR(A2)-YEAR(A1))*12+(MONTH(A2)-MONTH(A1))+(DAY(A2)/(DATE(YEAR(A2),MONTH(A2)+1,1)-DATE(YEAR(A2),MONTH(A2),1)))-((DAY(A1)-1)/(DATE(YEAR(A1),MONTH(A1)+1,1)-DATE(YEAR(A1),MONTH(A1),1)))

However I am trying to obtain a formula that allows me, from the months calculated between two dates, to know the exact number of days, that is, with the result obtained in cells A4 or A5, to calculate the number of days. One option I have considered is the function DAYS(A2,A1), but sometimes for the data I have, they only record the number of months with decimals (for example, 8.4 or 9.78), so I need to find the exact number of days from a number of months with decimals.

I would appreciate if you could help me with this query.

Improve this question
5
  • 1
    I don't understand how you are getting decimal days. the DAYS function should give you exactly the number of days between 2 days. Maybe edit your question and add examples of data that give decimal days.
    – Reddy Lutonadio
    Commented Jun 3 at 5:39
  • 1
    @jsotola - Writing an Excel formula in all capitals is allowed.
    – Ramhound
    Commented Jun 3 at 5:54
  • Dates are also intigers, but Excel can display them as a date, so if you just do date 2 - date 1 you should get an intiger, which should be the amount of days between the two.
    – Excellor
    Commented Jun 3 at 10:54
  • 1
    You cannot convert decimal months to exact number of days unless you know to which month the decimal portion applies.
    – Ron Rosenfeld
    Commented Jun 3 at 13:22
  • a formula like this could find the number of days for a month in the year 2024: =DAY(EOMONTH(INT(monthFraction)&"/01/24",0)). I leave it up to you to modify/construct the rest of the formua.
    – gns100
    Commented Jun 3 at 16:02

2 Answers 2

Reset to default
1

To calculate the exact number of days between two dates, you can use the following formula:

=ROUNDUP((A2-A1) + DAYS(DATE(YEAR(A2),MONTH(A2),1), DATE(YEAR(A2),MONTH(A2)+1,1)),0)

Summary:

  1. A2-A1 calculates the exact number of days between the two dates.
  2. DAYS(DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2),MONTH(A2)+1,1)) calculates the number of days in the month of the second date.
  3. Multiplying the difference in days by the number of days in the month of the second date gives the exact number of days between the two dates.
  4. ROUNDUP is used to round up the result to the nearest whole number.

This formula should work regardless of whether the number of months is recorded with decimals or not.

Improve this answer
2
  • @Hans_Schulze Thanks for your help, however I tried to use your expression and I get a too big value that does not correspond to the difference of days between the two dates, I do not know if I am making a mistake, but the value recorded exceeds 7k days!
    – Camila Sanchez
    Commented Jun 5 at 3:14
  • @CamilaSanchez, oops the "*" was supposed to be a "+", but I'm still not quite sure I'm testing it correctly. How exactly do you use your real month in a calculation, and what do you expect the answer to be given the above 8.4 or 9.78 numbers, assuming 2024?
    – Hans Schulze
    Commented Jun 7 at 22:04
0

The exact number of months (not fully tested, please check for an 'off-by-one' error).

A5 = (Year(A2)*12 + Month(A2)) - (Year(A1)*12 + Month(A1))

With that simple approach, it can be used for both 'approximate' and 'exact' solutions!

If the number of days is important:

A5 = (Year(A2)*12 + Month(A2)) - (Year(A1)*12 + Month(A1)) + (Day(A2) - Day(A1))/30

Noting that an average number of days per month is used to estimate the partial month (which can be positive or negative.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .