Forum Discussion

robert_leblancmention's avatar
robert_leblancmention
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's avatar
    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. 

     

     

davheili's avatar
davheili
Brass Contributor
Oct 29, 2021

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),

Resources