| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hollidays
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hollidays
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hollidays
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 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hollidays
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 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hollidays
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hollidays
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| dates: no weekends and no hollidays | Excel Worksheet Functions | |||
Hybrid Mode
