Date as YYYY-WW

[Gunnar Sandström]

Hi!
I want to have the NOW() formula show as 'yyyy-ww' in the sheet.
// Gunnar

 

[Frank Kabel]

Hi Gunnar
AFAIK can't be done with formating but you may try
=TEXT(NOW(),"YYYY") & "-" & TEXT(WEEKNUM(NOW()),"00")

 

[Harald Staff]

Hej Gunnar

Be very careful with weeknumbers -they are not the same in Europe and in US.
See
http://www.cpearson.com/excel/weeknum.htm
on this.
.... assuming you're in the eurpoean branch of Ericsson..

 

[Ron Rosenfeld]

Hi!
I want to have the NOW() formula show as 'yyyy-ww' in the sheet.
// Gunnar

You can use a UDF, but the result will be text and not a number that can be
used in other calculations.

===============
Function yw(dt As Date) As String
yw = Format(dt, "yyyy-ww")
End Function
==============

The VBA Format function has some optional arguments to define both the first
day of the week, and the first week of the year. You should check these out to
see which applies to your requirements.

I believe the following would conform to the ISO weeknumber convention:

=================
Function yw(dt As Date) As String
yw = Format(dt, "yyyy-ww", vbMonday, vbFirstFourDays)
End Function
================

--ron