# Greater than/Less than dates AND confusion

✭✭

I need to calculate a number based on if a date is greater than or less than dates AND based on a selection in another column.

example: If current date is between january-july from current year AND discipline dropdown is HHA then return number 6 in hours required and if the dropdown is PCA return 3.

Discipline column has two dropdowns: HHA, PCA

Current Date column

hours required

• ✭✭✭✭✭✭

Try something like this...

=IF(AND([Current Date]@row>= DATE(YEAR(TODAY()), 01, 01), [Current Date]@row< DATE(YEAR(TODAY()), 08,01)), IF(Discipline@row = "HHA", 6, IF(Discipline@row = "PCA", 3)))

• ✭✭✭✭✭✭

Try something like this...

=IF(AND([Current Date]@row>= DATE(YEAR(TODAY()), 01, 01), [Current Date]@row< DATE(YEAR(TODAY()), 08,01)), IF(Discipline@row = "HHA", 6, IF(Discipline@row = "PCA", 3)))

• ✭✭

It worked - amazing! Thank you Paul!!

• ✭✭

Paul, one more question for now please.

I have 3 date columns. trying to figure out a formula as follows.

expiration date

last update date

last work date

Expiration date should be "today" only if last work date is more than 60 days ago UNLESS last update date is from the past 60 days.

Thanks!

• ✭✭✭✭✭✭

Are you able to provide some mocked up examples for reference?

• ✭✭

I hope this is clearer

last work date - 07/12/22

last update - 7/12/22

expiration date -

if (hire date) and (last update) is more than 60 days ago from today then expiration date should be today.

on the other hand if hire date is more than 60 days ago but last update was within past 60 days then expiration is blank.

basically if somebody didnt work in the past two months and their file wasnt updated during that time then the expiration date should be today otherwise it should be blank.

• ✭✭✭✭✭✭

Try this...

=IF(AND([Last Work Date]@row < TODAY(-60), [Last Update]@row < TODAY(-60)), TODAY())

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!