Forum Discussion
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...
=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.
Here are two other variations:
=IF(INT((N4-M4)/7)<4,INT((N4-M4)/7)+1,"Graduated")
Or if you have an "Up-To-Date" Microsoft (Office) 365 subscription, a slightly faster version would be:
=LET(WeekDiff,INT((N4-M4)/7),
IF(WeekDiff<4,WeekDiff+1,"Graduated")
)
I'm making the assumption that your 22-29 was really intended to be 22-28.
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
It is housed on teams, will that field update the number automatically
- the other two do not work as the hire date (M4) has no correllation with the date in N4
=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.
- 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.