Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Unanswered: Format(Date) question/problem

    I'm trying to display data on a graph where the X-axis is the week of the year. Easy enough. Format([DfcncyInspctDt],""ww" "yy"") will give me "5 '06, 6 '06," etc. I'd liek to format it differently, though. Something like the date of the Sunday the week starts on. Any clue how I'd get that? So instead of "5 '06, 6 '06," I'd want "1/29/2006, 2/5/2006," etc. Thanks for reading. Have a great weekend.

  2. #2
    Join Date
    Feb 2005
    Posts
    333
    Justin

    I was just thinking about doing something lilke this for our time cards. Your question made me get off my butt. This should get you close

    Code:
    Public Function FindWeekStart()
    
    Dim temp As Date
    
    temp = Now()
    
    myday = Format(temp, "w")
    
    If Not myday = 1 Then
        temp = temp - myday
    End If
    
    End Function
    You're going to want to pass a value in for the date instead of using the temp=now() statement.

    BTW: This is Jamie

  3. #3
    Join Date
    Feb 2005
    Posts
    333
    Here's a better version
    Code:
    Public Function FindWeekStart(strDate As Date)
    
    myday = Format(strDate, "w")
    
    If Not myday = 1 Then
        FindWeekStart = strDate - myday + 1
    End If
    
    End Function
    I used this to test

    Code:
    Private Sub mydate()
    
        strDate = FindWeekStart(Now())
        myLabel = Format(strDate, "m/d/yy")
    
    End Sub

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    HI

    I'm not sure if the previous posts do what is require ie return the the stating Sunday of the week for ANY date (?). if so the this is as simple as it gets ?

    Code:
    Public Function FindWeekStart(dDate As Date) As Date
        FindWeekStart = dDate - Weekday(dDate, vbSunday) + 1
    End Function
    If you want the week to start on Monday then just use vbMonday in the weekday function.

    MTB

  5. #5
    Join Date
    Feb 2005
    Posts
    333
    Format(strDate, "w")
    and
    Weekday(dDate, vbSunday)
    return the same value. After looking at Mike's code I guess that you don't need the if statement that I used. If the date is a sunday then strDate - myday + 1 will just be strDate. Without ramblilng too much, both solutions do the same thing but Mike's is a little cleaner.

  6. #6
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Thanks, guys. all good now. jamie, i'll just call you next time. ha. thanks again for the help.

Posting Permissions

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