Convert weeks to first day of month (1 Viewer)

[dark11984]

Hi I am trying to convert a week which is entered in a text box on a form to the first day of the month in which it falls.

My text box (txtdw) is in the format yyww and I want it to be converted to 01/mm/yyyy.

example: 1401 would convert to 01/01/2014. 1406 would convert to 01/02/2014.

I am pretty close, I can get to the correct month but I can't work out how to change to the first day of the month.

DateAdd("D", Right(Me.txtDW, 2) * 7, DateSerial("20" & Left(Me.txtDW, 2), 1, 1))

 

[Isskint]

You pretty much have it. Just use another DateSerial() with the result you have gained.

Declare a Long variable to hold the result of your DateAdd() function. Declare another Long variable to hold the result of a second DateSerial() along with Year() and Month() EG DateSerial(Hour(),Month(),1)

Dim lngRawDate, lngTgtDate As Long

lngRawDate = DateAdd("D", Right(Me.txtDW, 2) * 7, DateSerial("20" & Left(Me.txtDW, 2), 1, 1))
lngTgtDate = DateSerial(Year(lngRawDate), Month(lngRawDate), 1)

 

[Mihail]

The first day is always 01. Nothing to calculate here.
Is something that I don't understand ?

 

[spikepl]

If this is something for yourself then it is one thing. If it is for business, then your method has shortcomings. Week 1 of 2014, according to ISO, begins on Dec 30, 2013 so the correct first day is 1 Dec 2013!

 

[dark11984]

thanks that worked Isskint!