How long to go?

[Ruddles]

Not really a question but replies always welcome...

I offer for your use my Excel formula for calculating how far in the future a given date is (cell A2 in this example). It returns a value in days, weeks, months or years depending how far in the future the date in A2 is, switching units from days to weeks at 35 days, from weeks to months at 20 weeks and from months to years after 11½ months.

=IF(A2>NOW(),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CHOOSE(MATCH(A2-TODAY(),{0;35;140;350}),ROUND((A2-TODAY()),0)&" day",ROUND((A2-TODAY())/7*2,0)/2&" week",ROUND((A2-TODAY())*2/30.6,0)/2&" month",ROUND((A2-TODAY())*4/365.25,0)/4&" year"),".25","¼"),".5","½"),".75","¾")&IF(CHOOSE(MATCH(A2-TODAY(),{0;35;140;350}),ROUND((A2-TODAY()),0),ROUND((A2-TODAY())/7*2,0)/2,ROUND((A2-TODAY())*2/30.6,0)/2,ROUND((A2-TODAY())*4/365.25,0)/4)=1,"","s"),"Past")

Let me know if you find it useful – or if you find any bugs!

 

[footoo]

I obtained the following formula from an AI website.
It’s a bit longer than yours (841 characters vs 440).
If the user can put up with results that do not recognize single numbers (e.g. I days), the AI formula can be reduced to 389 characters.
I have not fully tested the two formulas, but note that with a date of 1 June 2023 in A2, yours returns “5½ weeks” and the A! formula returns “5 weeks 2 days”.
Also, with a date of 31 May 2023 in A2, yours returns “5 weeks” and the AI formula returns “5 weeks 1 day”. The latter one is correct.

=IF(DATEDIF(TODAY(),A2,"D")=1,"1 day",IF(DATEDIF(TODAY(),A2,"D")<=35,DATEDIF(TODAY(),A2,"D")&" days",IF(DATEDIF(TODAY(),A2,"D")<=140,INT(DATEDIF(TODAY(),A2,"D")/7)&" week"&IF(INT(DATEDIF(TODAY(),A2,"D")/7)=1,"","s")&IF(MOD(DATEDIF(TODAY(),A2,"D"),7)=0,""," "&MOD(DATEDIF(TODAY(),A2,"D"),7)&" day"&IF(MOD(DATEDIF(TODAY(),A2,"D"),7)=1,"","s")),IF(DATEDIF(TODAY(),A2,"D")<=350,INT(DATEDIF(TODAY(),A2,"D")/30.44)&" month"&IF(INT(DATEDIF(TODAY(),A2,"D")/30.44)=1,"","s")&IF(MOD(DATEDIF(TODAY(),A2,"D"),30.44)=0,""," "&MOD(DATEDIF(TODAY(),A2,"D"),30.44)&" day"&IF(MOD(DATEDIF(TODAY(),A2,"D"),30.44)=1,"","s")),INT(DATEDIF(TODAY(),A2,"D")/365.25)&" year"&IF(INT(DATEDIF(TODAY(),A2,"D")/365.25)=1,"","s")&IF(MOD(DATEDIF(TODAY(),A2,"D"),365.25)=0,""," "&MOD(DATEDIF(TODAY(),A2,"D"),365.25)&" day"&IF(MOD(DATEDIF(TODAY(),A2,"D"),365.25)=1,"","s")))))

Note that the formula uses 365.25 days per year to account for leap years.

 

[Ruddles]

Thanks for the feedback.

I also try to account for leap years by dividing days by 365.25. Warning: this breaks down at the end of the century!

I round to halves and sometimes quarters rather than using combinations of different units. 5 weeks 2 days is nearer to 5½ weeks than to 5 weeks, so I use the ½ there.

The rounding does mean it's often slightly out, especially if the dates are a few weeks and months apart. I was trying to make it simple to understand at a glance, and in that respect the odd day becomes less significant as the date difference gets bigger. (It was designed for a specific worksheet where I'm looking at a column of future events in date order and need to scan quickly down to get a rough idea of how many are soon, a little way away, and well in the future.)

I shall have a play with the formula you posted, for sure.

 

[jdellasala]

You might want to look at the unrecognized DATEDIF function. It's a function from the older versions of Excel that's there for Lotus 123 compatibility. When you type "=DATEDIF" it won't appear in the functions list, and the opening parenthesis can't be added by hitting [Tab], but once you type "(" the function does appear, but without any help, but does show up in their Functions list.

 

[Ruddles]

=IF(DATEDIF(TODAY(),A2,"D")=1,"1 day",IF(DATEDIF(TODAY(),A2,"D")<=35,DATEDIF(TODAY(),A2,"D")&" days",IF(DATEDIF(TODAY(),A2,"D")<=140,INT(DATEDIF(TODAY(),A2,"D")/7)&" week"&IF(INT(DATEDIF(TODAY(),A2,"D")/7)=1,"","s")&IF(MOD(DATEDIF(TODAY(),A2,"D"),7)=0,""," "&MOD(DATEDIF(TODAY(),A2,"D"),7)&" day"&IF(MOD(DATEDIF(TODAY(),A2,"D"),7)=1,"","s")),IF(DATEDIF(TODAY(),A2,"D")<=350,INT(DATEDIF(TODAY(),A2,"D")/30.44)&" month"&IF(INT(DATEDIF(TODAY(),A2,"D")/30.44)=1,"","s")&IF(MOD(DATEDIF(TODAY(),A2,"D"),30.44)=0,""," "&MOD(DATEDIF(TODAY(),A2,"D"),30.44)&" day"&IF(MOD(DATEDIF(TODAY(),A2,"D"),30.44)=1,"","s")),INT(DATEDIF(TODAY(),A2,"D")/365.25)&" year"&IF(INT(DATEDIF(TODAY(),A2,"D")/365.25)=1,"","s")&IF(MOD(DATEDIF(TODAY(),A2,"D"),365.25)=0,""," "&MOD(DATEDIF(TODAY(),A2,"D"),365.25)&" day"&IF(MOD(DATEDIF(TODAY(),A2,"D"),365.25)=1,"","s")))))

I had to add a closing parenthesis as there was a mismatch one. Unfortunately for 2/2/2024 I got a result of 9 months 9.03999999999999 days.

 

[Ruddles]

Also, with a date of 31 May 2023 in A2, yours returns “5 weeks” and the AI formula returns “5 weeks 1 day”. The latter one is correct.

Yes, you're absolutely correct. I didn't make it clear enough in my original post: I use the formula to scan quickly down a column of dates and the further away a date is, the less accurate I need it to be. For example if something's five weeks away, the odd day or two doesn't really matter yet until the date starts to get closer. As long as it's accurate for the forthcoming month or so – I chose 35 days as the break point – that's accurate enough for my purposes, and when it's closer than 35 days, I want to see a more accurate figure. Similarly after 20 weeks, I only need to know to the nearest quarter of a month, because something nearly five months ahead really isn't 'on my radar' just yet.

The break points are visible in the =MATCH() functions and the places where accuracy is sacrificed is visible in the =ROUND() functions.

 

[Ruddles]

You might want to look at the unrecognized DATEDIF function. It's a function from the older versions of Excel that's there for Lotus 123 compatibility. When you type "=DATEDIF" it won't appear in the functions list, and the opening parenthesis can't be added by hitting [Tab], but once you type "(" the function does appear, but without any help, but does show up in their Functions list.

Yes, =DATEDIF() is certainly a useful function, and I do occasionally overlook it. If it disappears in the near future, a lot of my financial worksheets will fall over BIG TIME!

I deliberately didn't use it in my formula because I didn't want to perform arithmetic on fractions of weeks/months/years as Excel occasionally struggles with fractions. As you can see above, at one point the AI formula yielded a result of 9 months 9.03999999999999 days, and later on I also got 11 months 10.16 days. I work entirely in integers and only use fractions when I'm outputting the results. As I say, I'm happy to throw away small rounding errors for dates further ahead than 35 days, and slightly larger ones for dates further ahead than 20 weeks. That was deliberate as it suits my purposes, which is scanning quickly down a column of dates to get a quick impression of how far ahead the various events are.

 

[jdellasala]

Yes, =DATEDIF() is certainly a useful function, and I do occasionally overlook it. If it disappears in the near future, a lot of my financial worksheets will fall over BIG TIME!

I deliberately didn't use it in my formula because I didn't want to perform arithmetic on fractions of weeks/months/years as Excel occasionally struggles with fractions. As you can see above, at one point the AI formula yielded a result of 9 months 9.03999999999999 days, and later on I also got 11 months 10.16 days. I work entirely in integers and only use fractions when I'm outputting the results. As I say, I'm happy to throw away small rounding errors for dates further ahead than 35 days, and slightly larger ones for dates further ahead than 20 weeks. That was deliberate as it suits my purposes, which is scanning quickly down a column of dates to get a quick impression of how far ahead the various events are.

While I don't know why or like the fact that it's not completely supported like all other functions, it's been around since day 1, and highly doubt it will ever go away. I suspect its lack of integration has more to do with lawyers than with anything else!

 

[MARK858]

While I don't know why or like the fact that it's not completely supported like all other functions

Probably because of the issue with MD that they have never addressed completely

DATEDIF function - Microsoft Support

Calculates the number of days, months, or years between two dates.

support.microsoft.com