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.

robert_leblancmention

Copper Contributor

Oct 29, 2021

I think the revised works. TY. One more question. I forgot to mention display "0" if date has not occurred yet and display nothing if the field is blank (this way I can populate the field with formula all the way down.

It is housed on teams, will that field update the number automatically

robert_leblancmention

Copper Contributor

Oct 29, 2021

the other two do not work as the hire date (M4) has no correllation with the date in N4

OliverScheurich
Gold Contributor
Oct 29, 2021
robert_leblancmention
=IF(N4-M4<=7,1,IF(AND(N4-M4>=7,N4-M4<=13),2,IF(AND(N4-M4>=14,N4-M4<=21),3,IF(AND(N4-M4>=22,N4-M4<=29),4,"graduated"))))

Please try this formula. I assumed that you wanted to compare date of column N with date of today.
- robert_leblancmention
  Copper Contributor
  Oct 29, 2021
  probably my fault the first revised formula you sent works great.
  
  =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"))))
  
  Only relevant date is in N4.