Forum Discussion
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 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"
=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.
16 Replies
Try this to address the Blank Cells and Not happened yet situations:
=LET(WeekDiff,ROUNDUP((TODAY()-N4)/7,0),
IF(ISBLANK(N4),"",
IF(WeekDiff<=0,0,
IF(WeekDiff<=4,WeekDiff,"Graduated")))
)To refence cell M4 instead of tod, just change "Today()" to M4 Like this:
=LET(WeekDiff,ROUNDUP((M4-N4)/7,0),- that works. TY
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(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?
- 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.
The formula i sent you originally was wrong, sorry. I meanwhile sent you the corrected formula.
