Skip to main content
Question

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
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+14
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
------------------------------