Show date as day of the week

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!

Katheryn Young

Thanks JohnHinkle.  Worked perfectly for me.