forumla

[VbaHell]

Hello all

I hope you can help on this one

I want to work out what the cycle dates should be but not to include a Sat or Sun in the dates

A1 cell value will be a date
B1 cell value wll be the frequency per year
C1,D1,E1 etc would be the dates

Example

"A1" = 9/1/2018
"B1" = 1
"C1" = 9/1/2018

"A1" = 9/1/2018
"B1" = 12
"C1" = 9/1/2018
"D1" = 9/2/2018
"E1" = 9/3/2018
"F1" = 9/4/2-18
"G1" = 9/5/2018
"H1" = 11/6/2018 (This is because the 9th falls on the Saturday so the next availabe week day date is the 11th)

I am looking for some VBA code that will automate this and populate all the cells once after "A1" and "B1" values are entered. Maximum frequency is 52 (Weeks)

Any help would be great please

 

[JLGWhiz]

My system uses the m/d/yyyy format for the date, but I think this will work for you.

Sub t()
Dim i As Long, sh As Worksheet, cnt As Long, dy As Long, mo As Long, yr As Long
Set sh = ActiveSheet
cnt = sh.Range("B1").Value
dy = CLng(Format(sh.Range("A1").Value, "d"))
mo = Month(sh.Range("A1").Value)
yr = Year(sh.Range("A1").Value)
With sh
    For i = 1 To cnt
        .Cells(1, i + 2) = Format(DateSerial(yr, mo, dy), "d/m/yyyy")
        If Weekday(.Cells(1, i + 2).Value) = 1 Then .Cells(1, i + 2) = .Cells(1, i + 1).Value + 1
        If Weekday(.Cells(1, i + 2).Value) = 7 Then .Cells(1, i + 2) = .Cells(1, i + 2).Value + 2
        mo = mo + 1
    Next
End With
End Sub

 

[shg]

Another way, via UDF:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Start​

[/td][td="bgcolor:#F3F3F3"]

Freq​

[/td][td="bgcolor:#F3F3F3"]

Date1​

[/td][td="bgcolor:#F3F3F3"]

Date2​

[/td][td="bgcolor:#F3F3F3"]

Date3​

[/td][td="bgcolor:#F3F3F3"]

Date4​

[/td][td="bgcolor:#F3F3F3"]

Date5​

[/td][td="bgcolor:#F3F3F3"]

Date6​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

Sat 01 Sep 2018​

[/td][td]

30​

[/td][td="bgcolor:#E5E5E5"]

Sat 01 Sep 2018​

[/td][td="bgcolor:#E5E5E5"]

Thu 13 Sep 2018​

[/td][td="bgcolor:#E5E5E5"]

Tue 25 Sep 2018​

[/td][td="bgcolor:#E5E5E5"]

Mon 08 Oct 2018​

[/td][td="bgcolor:#E5E5E5"]

Fri 19 Oct 2018​

[/td][td="bgcolor:#E5E5E5"]

Wed 31 Oct 2018​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td]C2:BB2: {=DATES(A2, B2)}[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Function Dates(t As Date, iFreq As Long)
  Dim avtOut(1 To 52) As Variant
  Dim i As Long
  
  avtOut(1) = t
  
  With WorksheetFunction
    For i = 2 To iFreq
      avtOut(i) = .WorkDay(t + Int((i - 1) * 365 / iFreq) - 1, 1)
    Next i
    
    For i = i To 52
      avtOut(i) = vbNullString
    Next i
    
  End With
  Dates = avtOut
End Function

 

[shg]

This is a little more rigorous:

Function Dates(t As Date, ByVal iFreq As Long) as Variant
  Dim avtOut(1 To 52) As Variant
  Dim i             As Long

  If iFreq > 52 Then iFreq = 52
  
  With WorksheetFunction
    avtOut(1) = .WorkDay(t - 1, 1)
    For i = 2 To iFreq
      avtOut(i) = .WorkDay(t + Round((i - 1) * 365 / iFreq, 0) - 1, 1)
      Select Case Weekday(avtOut(i))
        Case vbSaturday
          avtOut(i) = avtOut(i) - 1
        Case vbSunday
          avtOut(i) = avtOut(i) + 1
      End Select
    Next i

    For i = i To 52
      avtOut(i) = vbNullString
    Next i

  End With
  Dates = avtOut
End Function

 

[VbaHell]

Thank you so much for all the effort in these replies, much appreciate all the support

 

[VbaHell]

Shg

I am a bit confused on this code, you mentioned Via UDF which I have never heard before, where would this code be placed please for it to work as there is no Sub() / end sub function on this

 

[VbaHell]

Hi Shg

I have copied and pasted the above code into a module

I then entered in "A2" the date and the Freq in "B2"

I then entered the formula "=DATES(A2, B2)" into "C2"

This gave me the date value from "A2"

If I change the freq to 4 should I copy the formula into "C2:F2" as this "=DATES(A2, B2)" or as this "=DATES($A2, $B2)"

At the moment both are not working so I must be doing something wrong on this

Could you help me please

 

[shg]

Select C2:BB2, paste the formula in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

 

[VbaHell]

Got It, Great

Can I be a pain and ask another question please

If I wanted the freq to be averaged over a year from the start date, IE start date 1/1/18 so end date would be 1/1/19, freq is 4 so the date would be every three months, is this possible please

 

[shg]

Why not just enter as 1/1/18 and 4?