CODEWITHSUNDEEP
vbaexcel
Thijsk
Thijsk

Reputation: 103

Excel 2013 VBA display 4 digits years with 2 digits weeks

Goodafternoon all,

Currently, I'm trying to fill a table with the following in the first row, which is needed for the VLOOKUP. I would like to have this row defined as "yyyy-ww", so for this week "2016-15". This works fine, however when it is earlier in the year, the weeks will be only 1 digit, "2016-1" instead of the expected "2016-01". Formatting does not work unfortunately.

Can anyone please help me out?

The formula is basically putting year, "-", current week together, then shifting up to 12 weeks minus (so, we're in week 15 now, I would like to have week 03 to 15 displayed). How do I get the weeks displayed in the right format?

 Range("A4").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-12)"
    Range("A5").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-11)"
    Range("A6").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-10)"
    Range("A7").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-9)"
    Range("A8").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-8)"
    Range("A9").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-7)"
    Range("A10").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-6)"
    Range("A11").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-5)"
    Range("A12").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-4)"
    Range("A13").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-3)"
    Range("A14").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-2)"
    Range("A15").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-1)"
    Range("A16").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7))"

Upvotes: 0

Views: 1554

Answers (4)

Phil
Phil

Reputation: 397

As you are adding the formulae in VBA, you may be better off getting the field value there instead:

range("A4").value = format(now,"yyyy") & "-" & format(format(dateadd("ww",-x,now),"ww"),"00")

where x is the number of weeks ago you want.

However, if you are intending to use the formula so that it will update without any interference then you can use the TEXT() function in the same way that VBA uses Format().

 Range("A4").FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"",TEXT((INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-12),"00"))"

A word of caution. VLookup works off the underlying field value, so if you are looking up from a date displayed as 2016-15, then the lookup will still fail. In this case, you might need to make sure that both dates are set to the first day of the working week to ensure a match.

Upvotes: 0

user3598756
user3598756

Reputation: 29421

you could have the desired format and shorten the code with the following

Range("A4").Resize(12).FormulaR1C1 = "=CONCATENATE(YEAR(TODAY()),""-"", TEXT(  INT((TODAY()-DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3)+WEEKDAY(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,3))+5)/7)-(12-rows(R4:R)) ,""00""))"

Upvotes: 2

asongtoruin
asongtoruin

Reputation: 10359

If you're willing to bring your formula into VBA, you can use DatePart to more efficiently extract the week number, and then Format to get the leading zero you desire.

The last line of your formula would become as follows:

Range("A16").FormulaR1C1 = Year(Now()) & "-" & Format(DatePart("ww", Now()), "00")

while the first would be

Range("A4").FormulaR1C1 = Year(Now()) & "-" & Format(DatePart("ww", Now()) - 12, "00")

Calling DatePart("ww",Now()) tells VBA to return the week part of todays date, wrapping this in Format tells it to output it in the format you seek.

A word of warning, however - this seems to return a value of 16 for today's date, so you may need to check your week definition against that used in DatePart, if you choose to use it.

Upvotes: 0

Thomas
Thomas

Reputation: 111

You can format you integer to allways have 2 digits.

Format(iMyInt, "00")

Upvotes: 0

Related Questions