Forum Discussion

KatherineOakey's avatar
KatherineOakey
Qrew Member
4 years ago

How to 'convert' Date/Time to Date to use DayofWeek?

I have a table with records, each of which has a field containing a Date/Time, say RegisterDate.

I want to be able to generate a report/chart that can sum the number of RegisterDates that are on Monday or Tuesday.  I don't care about the month or time... expecting a report with no more than 7 rows with a sum next to each day of the week.

I have tried adding a field that is a formula Date in the table. The syntax correct but when I just list the records with both the formula Date field and the Date/Time field, I see the Date/Time that contains the day of the week (which I'd really rather display other than a number but I'll deal with this later).  The column with the formula Date field displays nothing.

The formula I'm using is

DayOfWeek(ToDate([Related Event - Event Date & Time]))

Where Related Event - Event Date & Time  was selected via the Edit formula using a text field and then the Choose fields & functions dropdown before the formula box.

And it tells me it's expecting a date, not a number.  Do  need to add another function to convert the number to text?

------------------------------
Katherine Oakey
------------------------------
  • You need to convert the numeric output to a text value.  You would probably substitute your existing formula output for the [Date Field] as a variable in the formula below.

    Case(DayOfWeek([Date Field]),

    0,"Sunday",
    1,"Monday",
    2,"Tuesday",
    3,"Wednesday",
    4,"Thursday",
    5,"Friday",
    6,"Saturday")

    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------