CODEWITHSUNDEEP
google-sheetsformula
CamillasAlias
CamillasAlias

Reputation: 21

Google Sheets: IF and Date formula not working

I'm calculating annual holiday accrual.

All holiday resets at the start of the calendar year.

If a staff member started after 01.01.2017 they will have a lower holiday entitlement for the year.

I'm trying to create a formula which says:

If this cell's date is after 01.01.2017 return that cells date. Otherwise, set it at 01.01.2017.

(so basically, I don't care when they started if it was before 01 Jan 2017 because all my following calculations will be based off the first day of the year)

Here it is: 

=if(T21<date(1,1,2017),T21,"01/01/2017")

No matter what is in the cell, it is returning 01/01/2017.If I change the < to > it returns cell T21 in all cases.

Any ideas?

Thanks

Upvotes: 0

Views: 11599

Answers (2)

TheMaster
TheMaster

Reputation: 50443

"01.01.2017" is not a valid date in Google docs. You need to change . to /.

             =DATEVALUE(SUBSTITUTE(T21,".","/"))

            =IF(DATEVALUE(SUBSTITUTE(T21,".","/"))<DATEVALUE(date(2017,1,1)),T21,"01/01/2017")

Upvotes: 0

Karl_S
Karl_S

Reputation: 3554

You have the DATE() values mixed up. Try:

=if(T21<date(2017,1,1),T21,"01/01/2017")

DATE() is year, month, day

Also, you may want to use DATEVALUE() on the FALSE value to force the returned item to a date:

=if(T21<date(2017,1,1),T21,DATEVALUE("01/01/2017"))

Upvotes: 1

Related Questions