Date Format

[TFMR]

Dear All,

I have time sheet, which have Date and day columns in day column I gave the
ref. to A1 and change the format to "DDD", It shows the day e.g. Mon or Tue.
Now I want to apply formula that if b1="Sat" or "Sun" then return Off in c1
otherwise Morning in C1, but I am getting Morning in all cells.
Kindly advise what I have to mention in formula instead of Sat.
formula in C1 is "=if(b1="Sat"."Off",if(b1="Sun","Off","Morning"))
I tried full day but same result.

Thanks

 

[Max]

One way is to convert the underlying date value in B1 to "day" using TEXT
(Formulas read/evaluate underlying values, not displayed/formatted values)

In C1:
=IF(TEXT(B1,"ddd")="Sat","Off",IF(TEXT(B1,"ddd")="Sun","Off","Morning"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik

 

[Roger Govier]

Hi

You could use
=IF(WEEKDAY(B1,2)>5,"Off","Morning")