How can I format a date to a week format?

My present employer (as well as in my past employers) uses a week format for our payroll and for designating our work week (e.g., January 8, 2014 falls on "Week 2" of the year 2014 or December 9 to 13, 2013 falls on "Week 49" of year 2013.  Now, is there a category or a way to format a certain cell in Excel or Word to change a "Date" to a "Week" format?  It would be very useful for my report card/s.  Thanks in advance!
Answer
Answer
There is nothing built into Word that's as simple as Excel's WEEKNUM function.

You could modify one of the field codes in the Word Date Calculation Tutorial to convert the date to the day of the year, divide by 7, and add 1:

{ QUOTE { ASK  dt Date: } {SET yd{={dt \@ d}+INT(({dt \@ M}-0.986)*30.575)- IF({dt \@ M}>2,2-(MOD({dt \@ yy},4)=0)- (MOD({dt \@ yyyy},400)=0)+(MOD({dt \@ yy},100)=0),0)}} {=(yd/7)+1 \# 0} }

Alternatively, you could use a macro, with a Quick Access Toolbar button or a MACROBUTTON field to launch it. For example, if the date is in a cell of a Word table, this will change it:

Sub DateToWeekNumber()
    Dim rg As Range
    If Selection.Information(wdWithInTable) Then
        Set rg = Selection.Cells(1).Range
        rg.MoveEnd wdCharacter, -1  ' exclude cell marker
        If IsDate(rg.Text) Then
            rg.Text = "Week " & Format(rg.Text, "ww")
        End If
    Else
        MsgBox "Click on a date in a table cell.", , "Week Number"
    End If
End Sub

_____________________________
https://jay-freedman.info

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Jay,


I started toying with this earlier and got distracted.  Something isn't quite right.  Consider:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oDate As Date
oDate = "12/31/13"
MsgBox Format(oDate, "ww")

End Sub


which returns 53 :-(



The OPs original 12/13/13 returns 50 instead of his expected 49.  But I think maybe is should be 50.




Greg Maxey
***
Death smiles at us all, but all a man can do is smile back.


For more help with Word visit:
http://gregmaxey.com/word_tips.html

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated January 4, 2024 Views 428 Applies to: