how to fix a date format in an Excel macro? 2

[feipezi]

Hi,

I have a macro like the following. I need to create a bunch of dates like "nov-5-2014" to "nov-19-2014" with weekly interval. But the date presented will be like '11/5/14', '11/19/14'. But the following code will not make '2014' to '14'. Besides, I don't want an apostrophe in front of the date to make it character.

The format I set up is 'm/d/yy' but still give me '2014'. Why???

Any suggestions are welcome.

Thanks in advance.



Public calyear As Integer
Public cycyear As Integer
Public mm As Byte
Public dd As Byte
Public ic As Byte

'mm=7, dd=4, calyear=2014 and so on

Sub DefPubVars()
mm = Sheets("ParmTab").Cells(1, 1)
dd = Sheets("ParmTab").Cells(2, 1)
calyear = Sheets("ParmTab").Cells(3, 1)
cycyear = Sheets("ParmTab").Cells(4, 1)
End Sub

Sub ChngWeekBKWD(HWWks As Integer)
Dim str_ddmmmyy As Variant
Dim i, j As Long
Call DefPubVars
str_ddmmmyy = mm & "-" & dd & "-" & calyear
rw = ActiveCell.Row
col = ActiveCell.Column
For i = 0 To (HWWks - 1) * 7 Step 7
j = HWWks + col - 1 - i / 7
ActiveSheet.Cells(rw, j) = "'" & Format(CDate(str_ddmmmyy) - i, "m/d/yy")
Next
End Sub

Sub Week8()
Dim q As Long
wk8 = Array("v", "ba")
For q = LBound(wk8) To UBound(wk8)
Cells(2, wk8(q)).Activate
ChngWeekBKWD 8
Next q
End Sub

the output goes like this:

5/16/2014, 5/23/2014, 5/30/2014, 6/6/2014, 6/13/2014, 6/20/2014, 6/27/2014, 7/4/2014



But again, I want something like 5/16/14, not 5/16/2014.

 

[SkipVought]

Hi,

1) A date is a date.

2) you need no VBA

3) just enter your date 7/4/2014 (Independence Day!)

4) change the Number Format to "mmm-d-yy" for this column

 

[Andrzejek]

Excel 2010.[tt]
A1 - 5/16/2014
A2 - 5/17/2014[/tt]
Select both cells, start Macro
Right-clik on selected cells
Format cells...
Category: Date, Type: 3/14/01
OK
Stop macto
Alt-F11, and look at the code in Modules - Module1

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Oh yes, the formula for the next week's date is the previous date's reference + 7.

 

[PHV]

What about this ?

With ActiveSheet.Cells(rw, j)
  .Value = CDate(str_ddmmmyy) - i
  .NumberFormat = "m/d/yy"
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

If you insist on VBA all you need do is enter the year, month number and day in the DateSerial() function and assign that value to your first cell and set the number format to "mmm-d-yy". Then in a loop, add 7 to the previously assigned value. Pretty simple loop. Less than a dozen loc.

 

[feipezi]

Hi folks,

Thanks for spending time on my request.

The macro in the original email is to set up a 8 weeks columns, say from Col. "V" to Col. "AC", Col. "BA" to Col. "BH", which specified in the ARRAY() statements. For one column to the next, there would be 7 days apart but the ending date is variable with each production run. On top of that, one run can be 8 weeks; others may have 12 or 18 weeks. The starting column will change with it.

Some macros have the layout like "07-25-14 to 08-15-14" in one column and "08-22-14 to 09-12-14" in next. You can see it's a 4 weeks apart. We have 17 weeks interval too. All I have is '09-12-2014'. So I need to count back 4 weeks or 17 weeks and pick the starting week and ending week.

It's not like I have problems with one or 2 columns fixed. It's dynamic. It's hard to imagine doing it without macros.

I tried your ways. PHV's way really works. Even though I have no idea why my Format statements do not work. They look like the same.

Thanks a lot to all you.

 

[feipezi]

Hi,

PHV's way is not always working and no idea why. Andy's suggestion of recording a macro helps a lot. The recorded macro gave me a format like this:


.NumberFormat = "m/d/yy;@"


It worked in the situation where PHV's way not working.

Thanks again.

 

[feipezi]

One more question.

How can I make the following month upper case, without apostrophe?

Thanks again.

Sub ChngMonthBKWD(HWMons As Integer, num As Byte)
Dim str_ddmmmyy As Variant
Dim i, j As Long
Call DefPubVars
dummy_mm = mm - num
str_ddmmmyy = dummy_mm & "-" & dd & "-" & calyear
rw = ActiveCell.Row
col = ActiveCell.Column
For i = 0 To (HWMons - 1) * 30 Step 30
j = HWMons + col - 1 - i / 30
'ActiveSheet.Cells(rw, j) = UCase(Format(CDate(str_ddmmmyy) - i, "mmmyyyy"))


With ActiveSheet.Cells(rw, j)
.Value = CDate(str_ddmmmyy) - i
.NumberFormat = "mmmyyyy;@"
End With
ActiveSheet.Cells(rw, j) = UCase(ActiveSheet.Cells(rw, j))


Next
End Sub

 

[SkipVought]

Number format

"MMMMYYYY;@

 

[feipezi]

Skip,

It will give me something like "March2014" instead of "MAR2014", which is what I want. Btw, it's not full upper case either.

Thanks anyway.

 

[SkipVought]

Sorry


Number format

"MMMYYYY;@"