If Sat subtract one day, if Sunday subtract tow days

if you are just trying to get the last working day (Friday) of the week, you could use the WORKDAY function like this, if your date is in A1:

=WORKDAY(A1,-1)

in your formula, the IF function is not in the correct syntax. in case you want to continue with your approach, use this:

=IF(D65="sat",C65-1,IF(D65="sun",C65-2,""))

then format the cell containing the formula as DATE (the formula will result in a number, which needs to be converted into a DATE format). in Excel, DATE can be operated upon as a number; subtraction and addition are allowed, which simplifies much of the manipulation related to DATE.

it is probably better if you share your intent of this formula - there may be better solutions if the overall picture is known.

Given: IF(D65="Sat",=DATE(YEAR(C65),MONTH(C65),DAY(C65)-1)
1. If you have the words Saturday or Sunday in cell D65, you would have to test using:
a) the whole word, i.e., IF(D65="Saturday"... or,
b) The left three letters: IF(Left(D65,3)="Sat"...

2. The "=" in front of DATE does not belong.
3. Given a date in C65, just add or subtract a number from the date itself since dates in Excel are just the count of days since 01/01/1900 formatted by Excel as dates (03/31/2012 = 40999).

Therefore, try:

Formula:

Please Login or Register  to view this content.

Match looks in the {} list for the value that matches D65 and returns its place in the list. If the value in D65 is not in the list, MATCH returns #NA. The IFERROR() function is used to convert the #NA to zero (in this case)

So, the result is that the formula will subtract 1,2 or 0 from the date in C65.

Hi,

How about an example of what I think you want? See attached.