Welcome!

Show date as day of the week

How can I use this forumla =TEXT(A3, "ddd") ?? This formula calculates the day of the week for the date and returns the abbreviated name of the day of the week (Thu). Every time I try to use this formula, it says "not recognized".

Find more posts tagged with

Formulas

Conditional Formatting

Cell linking

Comments

Joseph Pereira

Thank you John Hinkle... I found your proposed solution to be the simplest and most graceful of all suggested.

Well done.

Lisa

Hi, Brett -

Can you tell me how your solution determines items due in the current calendar week? I need to create a report that shows past due items and items due in the current calendar week. I also need a report that shows items due in the next calendar week.

Thanks in advance!

Brett Evans

Lisa,

Here you go...

https://app.smartsheet.com/b/publish?EQBCT=283b5a9befe74d43a6a24900a614b050

pmweeks

I came across this page while looking for this workaround but I didn't like having to use a bunch of intermediary cells. Because modulo division also requires a workaround I eventually arrived at this monster

=IF(ISBLANK([APPT DATE]1), "", (IF((INT(([APPT DATE]1 - DATE(1999, 12, 26))) - INT(([APPT DATE]1 - DATE(1999, 12, 26)) / 7) * 7) = 0, "Sunday", IF((INT(([APPT DATE]1 - DATE(1999, 12, 26))) - INT(([APPT DATE]1 - DATE(1999, 12, 26)) / 7) * 7) = 1, "Monday", IF((INT(([APPT DATE]1 - DATE(1999, 12, 26))) - INT(([APPT DATE]1 - DATE(1999, 12, 26)) / 7) * 7) = 2, "Tuesday", IF((INT(([APPT DATE]1 - DATE(1999, 12, 26))) - INT(([APPT DATE]1 - DATE(1999, 12, 26)) / 7) * 7) = 3, "Wednesday", IF((INT(([APPT DATE]1 - DATE(1999, 12, 26))) - INT(([APPT DATE]1 - DATE(1999, 12, 26)) / 7) * 7) = 4, "Thursday", IF((INT(([APPT DATE]1 - DATE(1999, 12, 26))) - INT(([APPT DATE]1 - DATE(1999, 12, 26)) / 7) * 7) = 5, "Friday", IF((INT(([APPT DATE]1 - DATE(1999, 12, 26))) - INT(([APPT DATE]1 - DATE(1999, 12, 26)) / 7) * 7) = 6, "Saturday", IF(ISBLANK([APPT DATE]1), "", "")))))))) + ", " + [APPT DATE]1))

But this will reference a cell containing a date ([APPT DATE]1 in this case) in the format "04/13/18" and turn it into "Friday, 04/13/18". Only works for dates this century, though you could push the Sunday I subtract further back if you really needed to.

Laura

Thanks so much for sharing this! This is exactly what I was looking for and saved me so much time!

Bill Stadler

Excellent! Thank you for posting this!