Forum Discussion

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
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.

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"))))

Is this the formula you are looking for?

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
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
  Copper Contributor
  Oct 29, 2021
  how did you send it? I am new to the forum.
  - 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.