Greater than/Less than dates AND confusion

Tzippy

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

Paul Newcome

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

Paul Newcome

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

Tzippy

It worked - amazing! Thank you Paul!!

Tzippy

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!

Paul Newcome

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

Tzippy

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.

Paul Newcome

Try this...

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