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 open to other thoughts)
I have a date in cell N4, I want cell O4 to display "1" if <= 7 days, "2" if between 7 and 13, "3" if between 14 and 21, "4" if between 22 - 29, anything greater "graduated"
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.
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
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.
