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