Help

Re: Formulas for Date

Solved
Jump to Solution
534 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Pascal_Vallet
6 - Interface Innovator
6 - Interface Innovator

I would like to create a formulas to add the specific school year if the date in the column “DATE” is between DATE1 and DATE2

For example:

  • IF date is 5/20/2021 THEN in the column school_year I will have 2020-21
  • IF date is 9/20/2020 THEN in the column school_year I will have 2020-21
  • IF date is 9/20/2021 THEN in the column school_year I will have 2021-22
    What formulas should I use ?
1 Solution

Accepted Solutions
Rohit_Gandrakot
5 - Automation Enthusiast
5 - Automation Enthusiast

@Pascal_Vallet

You can probably use the WEEKNUM formula. The following formula should work.

IF(WEEKNUM(Date)>20,CONCATENATE(YEAR(Date),"-",YEAR(Date)+1),CONCATENATE(YEAR(Date)-1,"-",YEAR(Date)))

Where 20 is the week after which want to count it towards the next school year. It could be any week you want. Also within a week, it can be anyday (like Sunday or Monday). I ommited the day, so by default it’s assumed Sunday is the first day of the week.

See Solution in Thread

3 Replies 3
Rohit_Gandrakot
5 - Automation Enthusiast
5 - Automation Enthusiast

@Pascal_Vallet

You can probably use the WEEKNUM formula. The following formula should work.

IF(WEEKNUM(Date)>20,CONCATENATE(YEAR(Date),"-",YEAR(Date)+1),CONCATENATE(YEAR(Date)-1,"-",YEAR(Date)))

Where 20 is the week after which want to count it towards the next school year. It could be any week you want. Also within a week, it can be anyday (like Sunday or Monday). I ommited the day, so by default it’s assumed Sunday is the first day of the week.

Brillant ! It works perfectly, thanks !

Great. Please take a minute to mark the reply as the solution. Much appreciated!