new datefunction

J

jocke

hi can anyone help me with this.
i need a function that gives me the following:
year, weeknr, day of week

in the format
yyyywwdd

best regards
/jock
 
D

Dave Peterson

Or maybe:

=TEXT(A1,"YYYY") & TEXT(WEEKNUM(A1),"00") & TEXT(WEEKDAY(A1,1),"00")

Look at excel's help for =weekday() to find out what that second parm does.
1 means that Sunday=1, Monday=2,...,Saturday=7

Frank's formula will give you the day of the month (Nov 17, 2004 = 17).

(I wasn't sure what version you meant--especially with two digits.)
 
J

Jim May

Enter this UDF into a standard Module:

Function SpDate(MyDate As Date) As String
Dim MYr As String
Dim MWk As String
Dim MDy As String
MYr = Format(Year(MyDate),"0000")
MWk = Format(Round((MyDate - DateSerial(Year(MyDate), 1, 1)) / 7, 0), "00")
MDy = Format(Day(MyDate), "00")
SpDate = MYr & MWk & MDy
End Function

Then say you have 11/17/04 (Formated as Date) in Cell A1,
Enter into Cell B1 =SpDate(A1) >>> should result in 20044617
HTH
 
J

Jim May

To separate yyyy-ww-dd using Hyphens (-) use

SpDate = MYr & "-" & MWk & "-" & MDy

HTH

Jim May said:
Enter this UDF into a standard Module:

Function SpDate(MyDate As Date) As String
Dim MYr As String
Dim MWk As String
Dim MDy As String
MYr = Format(Year(MyDate),"0000")
MWk = Format(Round((MyDate - DateSerial(Year(MyDate), 1, 1)) / 7, 0), "00")
MDy = Format(Day(MyDate), "00")
SpDate = MYr & MWk & MDy
End Function

Then say you have 11/17/04 (Formated as Date) in Cell A1,
Enter into Cell B1 =SpDate(A1) >>> should result in 20044617
HTH
Click to expand...
 
Top