How can i use IF, AND in a date formuls?

Brandon Morales

I currently have a formula that looks at a column called "Week #" and takes the entry and converts it to a date that represents the first monday of the corresponding week. This helps me plan work a number of weeks out. the problem is that as i get towards the end of the year, i need the date produced to show the week for the next year but my formula produces the date for the current year.

Im wondering if i can somehow use an if/and formula that says that if today is greater that July 1st and the week number is between 1 and 14 then add one year to the date, otherwise add zero.

Would that work? If so, how do I manipulate my current formula to do just that?

Here is my current formula:

=DATE(YEAR(TODAY()), 1, 1) + (([Week #]@row - 1) * 7) - WEEKDAY(DATE(YEAR(TODAY()), 1, 1)) + 2

=Chris Palmer

Hi @Brandon Morales,

Something like this perhaps?

=DATE(

    YEAR(TODAY()) + IF(AND(TODAY() > DATE(YEAR(TODAY()), 7, 1), [Week #]@row <= 14), 1, 0),

    1, 1) 


+ 

(([Week #]@row - 1) * 7) 

- WEEKDAY(DATE(

    YEAR(TODAY()) + IF(AND(TODAY() > DATE(YEAR(TODAY()), 7, 1), [Week #]@row <= 14), 1, 0),

    1, 1))+ 2