Forum Discussion

robert_leblancmention's avatar
robert_leblancmention
Copper Contributor
Oct 29, 2021
Solved

Display number based on number of days past cell date

I am working on trying to get an output for training classes.  Based on the date they start training I want to automatically populate which week of training they are in. I am including weekends (or o...
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Oct 29, 2021

    robert_leblancmention 

    =IF(TODAY()-N4<=7,1,IF(AND(TODAY()-N4>=7,TODAY()-N4<=13),2,IF(AND(TODAY()-N4>=14,TODAY()-N4<=21),3,IF(AND(TODAY()-N4>=22,TODAY()-N4<=29),4,"graduated"))))

     

    I sent it a few minutes ago. Here it is again. 

     

     

robert_leblancmention's avatar
robert_leblancmention
Copper Contributor
Oct 29, 2021
I am not sure it works exactly. For example "N" column has a date of 10/11/2021 and it displays "1", but should display "4". as that is 28 days from the date in the N column. and some of the cells almost a year old are showing a number rather than graduated. not sure why.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 29, 2021

robert_leblancmention 

The formula i sent you originally was wrong, sorry. I meanwhile sent you the corrected formula.

 

  • robert_leblancmention's avatar
    robert_leblancmention
    Copper Contributor
    Oct 29, 2021
    how did you send it? I am new to the forum.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 29, 2021

      robert_leblancmention 

      As a comment, the best way to exchange formulae is to share simple sample Excel file without sensitive content. Screenshots and like are not very useful and not very informative. Formulae could be duplicated (and better if they are) in the post to illustrate the discussion, but they could be for different locale, could include wrong (for formulae) HTML symbols, etc. Excel file converts everything automatically, thus you are locale independent.

      • robert_leblancmention's avatar
        robert_leblancmention
        Copper Contributor
        Oct 29, 2021
        thank you for the advice. I think it mentioned something about including screenshot to assist, so that is why I put it.
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Oct 29, 2021

      robert_leblancmention 

      =IF(TODAY()-N4<=7,1,IF(AND(TODAY()-N4>=7,TODAY()-N4<=13),2,IF(AND(TODAY()-N4>=14,TODAY()-N4<=21),3,IF(AND(TODAY()-N4>=22,TODAY()-N4<=29),4,"graduated"))))

       

      I sent it a few minutes ago. Here it is again. 

       

       

Resources