Holiday Function - Show/Hide 1st of May.

[gnaske]

Hi Forum.

This description is in the attached Test too.

I need to show the 1st of May in B4, depending of the value in D10 is Yes or No.

The Case DateSerial(InputYear, 5, 1): HelligdagsNavn = "1. Maj" is working in the Module Function named X_Helligdage_Funktion.

Instead I need something as the two IF THEN below or maybe an IF THEN ELSE instead and only use the Yes.
- Rem If Sheets("Indstil").Range("D10") = "Yes" Then Case DateSerial(InputYear, 5, 1): HelligdagsNavn = "1. Maj"
- Rem If Sheets("Indstil").Range("D10") = "No" Then Case DateSerial(InputYear, 5, 1): HelligdagsNavn = ""

Anybody who can help me define the Yes(No) in the VBA Function ?

Thank you in advance.

Ib

[Greg M]

Hi there,

Does the following formula in Cell B4 do what you need?

=IF(AND(A4 <> "",  D10 = "Yes"),  HelligdagsNavn(A4,0,0),  "")

Hope this helps - please let me know how you get on.

Regards,

Greg M

[gnaske]

Hi Greg.

Is the code you send a code I can place in a VBA Function ?

I Denmark is the 1st of May is a holiday for some people and a normal working day for others.
The holiday is called "1st of May" as e.g. the 1/1 is called New Years Day.

If you open the attached test sheet, you'll see there is some date's in column A and to the right for the date is the holiday-name in column B.

Depending of the value in D10 is Yes / No, it should show the holiday 1st of May in B4.

Regards
Ib

[gnaske]

This attached Test probably show what I mean and need in a better way

[Greg M]

Hi there,

Many thanks for that very clear description of your requirements.

Take a look at the attached workbook and see if it does what you need. I have rewritten your "HelligdagsNavn" routine as follows:

Function HelligdagsNavn(lngdate As Long, InclSaturdays As Boolean, _
                        InclSundays As Boolean, Optional sYesNo As String) As String

'   Returnerer Sand hvis lngDate er en Dansk HelligdagsNavn/nationaldag
'   (valgfri inkludering af lordag/sondag)
'   bruger funktionen Paskedag

    Dim InputYear As Integer, PD As Long    '    <<<  The "OK" variable is not used

    If lngdate <= 0 Then
        lngdate = Date
    End If

    InputYear = Year(lngdate)
    PD = Paskedag(InputYear)

    Select Case lngdate ' Tester nedenstaende pastande mod datoen

        Case DateSerial(InputYear, 1, 1): HelligdagsNavn = "Nytarsdag"
        Case PD - 3: HelligdagsNavn = "Skaertorsdag"
        Case PD - 2: HelligdagsNavn = "Langfredag"
        Case PD: HelligdagsNavn = "Paskedag"
        Case PD + 1: HelligdagsNavn = "2. Paskedag"
        Case PD + 26: HelligdagsNavn = "Store Bededag"
        Case PD + 39: HelligdagsNavn = "Kr. Himmelfartsdag"
        Case PD + 49: HelligdagsNavn = "Pinsedag"
        Case PD + 50: HelligdagsNavn = "2. Pinsedag"
        Case DateSerial(InputYear, 12, 24): HelligdagsNavn = "Julaftensdag"
        Case DateSerial(InputYear, 12, 25): HelligdagsNavn = "1. Juledag"
        Case DateSerial(InputYear, 12, 26): HelligdagsNavn = "2. Juledag"
        Case DateSerial(InputYear, 12, 31): HelligdagsNavn = "Nytarsaftensdag"
        Case DateSerial(InputYear, 6, 5): HelligdagsNavn = "Grundlovsdag"

        Case DateSerial(InputYear, 5, 1): HelligdagsNavn = "1. Maj"

            If UCase(sYesNo) = "YES" Then
                  HelligdagsNavn = "1. Maj"
            Else: HelligdagsNavn = ""
            End If

        Case Else

    End Select

    If InclSaturdays Then       '   Tester lordage, hvis de skal medtages

        If Weekday(lngdate, vbMonday) = 6 Then
            HelligdagsNavn = HelligdagsNavn & " Lordag"
        End If

    End If

    If InclSundays Then         '   Tester sondage, hvis de skal medtages

        If Weekday(lngdate, vbMonday) = 7 Then
            HelligdagsNavn = HelligdagsNavn & " Sondag"
        End If

    End If

End Function

The optional argument "sYesNo" is used only in Cell B4 (for the date of 1 May yyyy) as follows:

=HelligdagsNavn(A4,  0,  0,  D10)

where D10 is the cell which contains the Yes / No value


For all other cells the function is entered without the optional argument, e.g.

=HelligdagsNavn(A5,  0,  0)

As far as I can tell, it's not really necessary to include your error handling when the function is used, i.e.

=IFERROR(IF(A5="","",HelligdagsNavn(A5,0,0)),"")

Please note that in order to get the site to accept this post I have had to use the highlighted "English" letters as replacements for the original Danish ones - the attached workbook contains the Danish characters copied from your workbook.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[gnaske]

Hi Greg.

The sheet will be used by a lot of people in my company.
Some have the 1th of May as a holiday and others don't.

I made a Userform with some settings too and one of the setting is a chechbox with the text - Is the 1st of May a holiday ?
Depending of the checkbox is checked or not, it will set a "Yes" in the Setting Tab D10 and then show the 1st of May as a holiday.

The sheet can also change the first date in the date column, which mean the 1st of May can be i any of the rows with a date and not only in B4.
That's why I needed at code in the function, which find the 1st of May in column A and then marked it as a holiday if D10 is "Yes".

I believe I need something as - If Sheets("Indstil").Range("D10") ="Yes" Then HelligdagsNavn = "1. Maj" Else HelligdagsNavn = "".
But I don't know


Kind regards.
Ib

[gnaske]

I had been thinking if something like this can be used, but I can't get i work

        Case DateSerial(InputYear, 5, 1): HelligdagsNavn = "1. Maj"
            
            If UCase(Sheets("Indstil").Range("D10")) = "Yes" Then
                HelligdagsNavn = "1. Maj"
                Else: HelligdagsNavn = ""
            End If

Ib

[Greg M]

Hi again,

Thanks for your feedback.

I'll have a look to see what I can do, but I'll be away for a few days so it will probably be Monday/Tuesday next before I can get back to you.

Regards,

Greg M

[gnaske]

Hi Greg.

I just put this code in all the cells with the holiday text (Column B).
It solves my problem, no matter the starting date in column A, first cell.
Look the attached

- A7 - Is the cell with the floating date to the left.
- Indstil!$D$10 - Is the date 1st of May on my settings tab, I use to compare with all the dates in column A.
- Indstil!$D$11 - Is the YES on my settings tab, I can change with the CheckBox in the settings Userform.

=If(A7=Indstil!$D$10;HelligdagsNavn(A7; 0; 0; Indstil!$D$11);HelligdagsNavn(A7; 0; 0))

Thank you very much for your patience and help

Kind regards
Ib

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome, and congratulations on sorting out the finer points out by yourself.

Best regards,

Greg M

[gnaske]

Hi Greg.

I could attach the final sheet, but it open it's own Ribbon and hide Excels Ribbon and it's all in Danish.
I no sure if you'll get anything out of it at all.

Menu med tal.png

1) Different way to save.
2) Settings - With the 1. of May selector.
3) Open the Useerform, where The User type the data into.
4) Change date.
5) Create PDF.
6) Open file explore to the PDF saving map.
7) Print on paper - Print Dialog open, with all the printes on the PC and network.
8) Delete all typed data - In the Userform, Ribbon no. 3.
9) A little information.
10a) System requirements.
10b) Version no. and About the sheet. (who made it).
11) Is a Admin Tab and it require a password to open.

This pic of the Ribbon and the text was user in the User Manual


Ones again, it's me who thanks.

Kind regard
Ib