Current Formula - Working

=IF(DATEDIF(TODAY(),U6,"d") > 7,ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0)&IF(ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0) > 1," Weeks and "," Week and ")&DATEDIF(TODAY(),U6,"d")-(ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0)*7)&IF(DATEDIF(TODAY(),U6,"d")-(ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0)*7) > 1," days"," day"),IF(DATEDIF(TODAY(),U6,"d") = 1,"Tomorrow",DATEDIF(TODAY(),U6,"d")&IF(DATEDIF(TODAY(),U6,"d") > 1," days"," day")))

New Formula - Broken

=IF(DATEDIF(TODAY(),U6,"d") > 7,IF(DATEDIF(TODAY(),U6,"d")/7 = 2,"Today",ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0)&IF(ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0) > 1," Weeks and "," Week and ")&DATEDIF(TODAY(),U6,"d")-(ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0)*7)&IF(DATEDIF(TODAY(),U6,"d")-(ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0)*7) > 1,"days","day"),),IF(DATEDIF(TODAY(),U6,"d") = 1,"Tomorrow",DATEDIF(TODAY(),U6,"d")&IF(DATEDIF(TODAY(),U6,"d") > 1," days"," day")))

What I'm Trying To Add

I've got "Tomorrow" working, however now I wish to implement a "Today". To work out if it is "Today", I add the length between the payments as it jumps to the next date if it is today, or in my above version, I go for the weeks between method.

I've broken up my function several times, however I cannot see the problem. Is there a limit to the length of a formula maybe?

  • 2
    Change "days","day"),), to "days","day")), ;). – shA.t Jan 18 '17 at 11:17
  • @shA.t please add your comment as an answer – Kevin Anthony Oppegaard Rose Jan 19 '17 at 8:16
  • @KevinAnthonyOppegaardRose Ok, and Done! – shA.t Jan 19 '17 at 19:05

Problem is in this part of your formula:

IF(DATEDIF(TODAY(),U6,"d")-(ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0)*7) > 1 ,"days","day"),)
'  ^1st                                                                    ^2nd   ^3rd   ^4th

That an IF has 4 arguments, an IF statement has just 3 arguments, So just use it like this:

IF(DATEDIF(TODAY(),U6,"d")-(ROUNDDOWN(DATEDIF(TODAY(),U6,"d")/7,0)*7) > 1 ,"days","day"))

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.