ExcelBanter - hollidays

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - hollidays (https://www.excelbanter.com/excel-programming/372209-hollidays.html)

Hi again,
I´ve done this in vbscript:
[KOD]longfred = DateAdd("d", -2, esterday)
esterafton = DateAdd("d", -1, esterday)
estermond = DateAdd("d", 1, esterday)
flygdag = DateAdd("d", 39, esterday)
pingstd = DateAdd("d", 49, esterday)
pingstaftn = DateAdd("d", -1, pingstd) [/KOD]
Is it possible to do that in Excek/VBA??

Thanks in advance
//Peter

Peter,
You can use the same code.
You have the advantage of being able to declare you variables as dates to
reduce errors.
Dim LongFred As Date
Dim EsterAfton As Date etc

NickHK

"peter the swede" wrote in message
oups.com...
Hi again,
I´ve done this in vbscript:
[KOD]longfred = DateAdd("d", -2, esterday)
esterafton = DateAdd("d", -1, esterday)
estermond = DateAdd("d", 1, esterday)
flygdag = DateAdd("d", 39, esterday)
pingstd = DateAdd("d", 49, esterday)
pingstaftn = DateAdd("d", -1, pingstd) [/KOD]
Is it possible to do that in Excek/VBA??

Thanks in advance
//Peter

Thanks Nick,
a little more help please:
If I´ve this function:

Code:

Function Esterday(Year As Integer, Optional Returtyp As Integer) As

Variant

Dim A As Integer, B As Integer, C As Integer, D As Integer

Dim E As Integer, F As Integer, G As Integer, H As Integer

Dim I As Integer, K As Integer, L As Integer, M As Integer

Dim P As Integer, Q As Integer, intDatum As Integer, strMonth As String



A = Year Mod 19

B = Int(Year / 100)

C = Year Mod 100

D = Int(B / 4)

E = B Mod 4

F = Int(B + 8) / 25

G = Int((B - F + 1) / 3)

H = ((19 * A + B - D - G + 15) Mod 30)

I = Int(C / 4)

K = C Mod 4

L = ((32 + 2 * E + 2 * I - H - K) Mod 7)

M = Int((A + 11 * H + 22 * L) / 451)

P = Int((H + L - 7 * M + 114) / 31)

Q = ((H + L - 7 * M + 114) Mod 31)

If P = 3 Then strMonth = "Mars"

If P = 4 Then strMonth = "April"

intDatum = Q + 1

If Returtyp = 2 Then

Esterday = Year & " " & strMonth & " " & intDatum

Else

Esterday = DateValue(Year & "-" & "0" & P & "-" & intDatum)

End If



End Function

And have the year in cell D11 on page hollidays
can you give me an exemple what it looks like?

Thanks in advance
//Peter

NickHK skrev:

Peter,
You can use the same code.
You have the advantage of being able to declare you variables as dates to
reduce errors.
Dim LongFred As Date
Dim EsterAfton As Date etc

NickHK

"peter the swede" wrote in message
oups.com...
Hi again,
I´ve done this in vbscript:
[KOD]longfred = DateAdd("d", -2, esterday)
esterafton = DateAdd("d", -1, esterday)
estermond = DateAdd("d", 1, esterday)
flygdag = DateAdd("d", 39, esterday)
pingstd = DateAdd("d", 49, esterday)
pingstaftn = DateAdd("d", -1, pingstd) [/KOD]
Is it possible to do that in Excek/VBA??

Thanks in advance
//Peter

Peter,
If you make this function Public in a module, then you can call it a
worksheet cell.
From Excel press Alt+F11 to open the VBA IDE.
On the project tree for your workbook, right-click Insert Module.
Paste this function code.
Change the signature to read:
Public Function Esterday(Year As Integer, Optional Returtyp As Integer) As
Variant

Then from a cell in your worksheet, type (without the quote marks) :
"=Esterday(D11,1)"

NickHK

"peter the swede" wrote in message
ps.com...
Thanks Nick,
a little more help please:
If I´ve this function:

Code:

Function Esterday(Year As Integer, Optional Returtyp As Integer) As

Variant

Dim A As Integer, B As Integer, C As Integer, D As Integer

Dim E As Integer, F As Integer, G As Integer, H As Integer

Dim I As Integer, K As Integer, L As Integer, M As Integer

Dim P As Integer, Q As Integer, intDatum As Integer, strMonth As String



A = Year Mod 19

B = Int(Year / 100)

C = Year Mod 100

D = Int(B / 4)

E = B Mod 4

F = Int(B + 8) / 25

G = Int((B - F + 1) / 3)

H = ((19 * A + B - D - G + 15) Mod 30)

I = Int(C / 4)

K = C Mod 4

L = ((32 + 2 * E + 2 * I - H - K) Mod 7)

M = Int((A + 11 * H + 22 * L) / 451)

P = Int((H + L - 7 * M + 114) / 31)

Q = ((H + L - 7 * M + 114) Mod 31)

If P = 3 Then strMonth = "Mars"

If P = 4 Then strMonth = "April"

intDatum = Q + 1

If Returtyp = 2 Then

Esterday = Year & " " & strMonth & " " & intDatum

Else

Esterday = DateValue(Year & "-" & "0" & P & "-" & intDatum)

End If



End Function

Thx NickHK
two more questions please:-)
1) what do 1 means in "=Esterday(D11,1)"
2) can I write: "=Esterday(holliday!D11,1)"
NickHK skrev:

Peter,
If you make this function Public in a module, then you can call it a
worksheet cell.
From Excel press Alt+F11 to open the VBA IDE.
On the project tree for your workbook, right-click Insert Module.
Paste this function code.
Change the signature to read:
Public Function Esterday(Year As Integer, Optional Returtyp As Integer) As
Variant

Then from a cell in your worksheet, type (without the quote marks) :
"=Esterday(D11,1)"

NickHK

"peter the swede" wrote in message
ps.com...
Thanks Nick,
a little more help please:
If I´ve this function:

Code:

Function Esterday(Year As Integer, Optional Returtyp As Integer) As Variant Dim A As Integer, B As Integer, C As Integer, D As Integer Dim E As Integer, F As Integer, G As Integer, H As Integer Dim I As Integer, K As Integer, L As Integer, M As Integer Dim P As Integer, Q As Integer, intDatum As Integer, strMonth As String A = Year Mod 19 B = Int(Year / 100) C = Year Mod 100 D = Int(B / 4) E = B Mod 4 F = Int(B + 8) / 25 G = Int((B - F + 1) / 3) H = ((19 * A + B - D - G + 15) Mod 30) I = Int(C / 4) K = C Mod 4 L = ((32 + 2 * E + 2 * I - H - K) Mod 7) M = Int((A + 11 * H + 22 * L) / 451) P = Int((H + L - 7 * M + 114) / 31) Q = ((H + L - 7 * M + 114) Mod 31) If P = 3 Then strMonth = "Mars" If P = 4 Then strMonth = "April" intDatum = Q + 1 If Returtyp = 2 Then Esterday = Year & " " & strMonth & " " & intDatum Else Esterday = DateValue(Year & "-" & "0" & P & "-" & intDatum) End If End Function

And have the year in cell D11 on page hollidays
can you give me an exemple what it looks like?

Thanks in advance
//Peter

NickHK skrev:

Peter,
You can use the same code.
You have the advantage of being able to declare you variables as dates to
reduce errors.
Dim LongFred As Date
Dim EsterAfton As Date etc

NickHK

"peter the swede" wrote in message
oups.com...
Hi again,
I´ve done this in vbscript:
[KOD]longfred = DateAdd("d", -2, esterday)
esterafton = DateAdd("d", -1, esterday)
estermond = DateAdd("d", 1, esterday)
flygdag = DateAdd("d", 39, esterday)
pingstd = DateAdd("d", 49, esterday)
pingstaftn = DateAdd("d", -1, pingstd) [/KOD]
Is it possible to do that in Excek/VBA??

Thanks in advance
//Peter

Peter,
1 - Your function has 2 arguments, one Optional. So the call to your
function matches the function's signature.
2 - You can pass any valid cell reference that contains the Year you want to
get Easter day for.
You can also pass a value ; "=Esterday(6,1)"

NickHK

"peter the swede" wrote in message
ups.com...
Thx NickHK
two more questions please:-)
1) what do 1 means in "=Esterday(D11,1)"
2) can I write: "=Esterday(holliday!D11,1)"
NickHK skrev:

Peter,
If you make this function Public in a module, then you can call it a
worksheet cell.
From Excel press Alt+F11 to open the VBA IDE.
On the project tree for your workbook, right-click Insert Module.
Paste this function code.
Change the signature to read:
Public Function Esterday(Year As Integer, Optional Returtyp As Integer) As
Variant

Then from a cell in your worksheet, type (without the quote marks) :
"=Esterday(D11,1)"

NickHK

"peter the swede" wrote in message
ps.com...
Thanks Nick,
a little more help please:
If I´ve this function:

Code:

Function Esterday(Year As Integer, Optional Returtyp As Integer) As Variant Dim A As Integer, B As Integer, C As Integer, D As Integer Dim E As Integer, F As Integer, G As Integer, H As Integer Dim I As Integer, K As Integer, L As Integer, M As Integer Dim P As Integer, Q As Integer, intDatum As Integer, strMonth As String A = Year Mod 19 B = Int(Year / 100) C = Year Mod 100 D = Int(B / 4) E = B Mod 4 F = Int(B + 8) / 25 G = Int((B - F + 1) / 3) H = ((19 * A + B - D - G + 15) Mod 30) I = Int(C / 4) K = C Mod 4 L = ((32 + 2 * E + 2 * I - H - K) Mod 7) M = Int((A + 11 * H + 22 * L) / 451) P = Int((H + L - 7 * M + 114) / 31) Q = ((H + L - 7 * M + 114) Mod 31) If P = 3 Then strMonth = "Mars" If P = 4 Then strMonth = "April" intDatum = Q + 1 If Returtyp = 2 Then Esterday = Year & " " & strMonth & " " & intDatum Else Esterday = DateValue(Year & "-" & "0" & P & "-" & intDatum) End If End Function

And have the year in cell D11 on page hollidays
can you give me an exemple what it looks like?

Thanks in advance
//Peter

NickHK skrev:

Peter,
You can use the same code.
You have the advantage of being able to declare you variables as dates
to
reduce errors.
Dim LongFred As Date
Dim EsterAfton As Date etc

NickHK

"peter the swede" wrote in message
oups.com...
Hi again,
I´ve done this in vbscript:
[KOD]longfred = DateAdd("d", -2, esterday)
esterafton = DateAdd("d", -1, esterday)
estermond = DateAdd("d", 1, esterday)
flygdag = DateAdd("d", 39, esterday)
pingstd = DateAdd("d", 49, esterday)
pingstaftn = DateAdd("d", -1, pingstd) [/KOD]
Is it possible to do that in Excek/VBA??

Thanks in advance
//Peter