Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003

    Unanswered: days of week, problem?

    I have a Query in which I want to calculate days of a week to start with “Monday”. Field expression is:

    Weekdays: Format ([Date];"dddd"; 2; 3)

    When this Query is in PivotTableView days of week started with Sunday. And in filter menu they are sorted alphabetically.

    What can I do to start days from Monday and sorted them in filter like Monday, Tuesday, Wednesday….


  2. #2
    Join Date
    Mar 2007
    intWeekday = Weekday(Date(),2)
    strWeekday = WeekdayName(Weekday(Date(),False,2))

    the 2 stands for that the week start with Monday.
    trying to combine these 2 formulas wont work, you'll need to fields: one to sort on day number and one to display the name of the week.

  3. #3
    Join Date
    Jan 2007
    California, USA
    If you define your days twice, once as a number, and once as the full name, or put off using the full name until you get inside the PivotTableView (which I have never used, so I don't know if you can or not wait until then). So, define the date as a number for the number of the day of the week like so: Format(MyDate, "w", vbMonday). That will give you the days of the week starting with Monday being 1, Tuesday being 2, etc. Then you can sort on this number, rather than on Monday, Tuesday, etc. and they will be in the order you want. As for their names, I don't, as I said, know how to get them where they are not sorted.
    Hope this helps at least a little,

  4. #4
    Join Date
    Jun 2003
    Tenk you BMore and GolferGuy!

    It’s unbelievable that no one of functions woks properly in PivotTableView. All gave the same result – starting days from Sunday.

    For the present I use your advice for having 2 fields: one for number of week day and another for weekday name. When used together (simultaneously) - days starts from 1 and respectively the first value is Monday ;-)

    I think this is enough and more important then sorting the days ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts