Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SURE INSERTING A WORKING DATE (2000 sr 1)

    .. My prob is when i insert a date in this format DD/MM/AAAA is a working day
    for example i insert in D1(preformatted dd/mm/yyyy) 25/12/2004 is not a workin day make to appeaR a msg box "attention not a work day, repeat!"
    my idea is to insert in a column a all workin day and find the relative value in that... or you have another way?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    You can use Validation for this, together with the WORKDAY function from the Analysis Toolpak add-in. Unfortunately, I don't know what this function is called in Italian, and it won't help if I attach a workbook, for Analysis Toolpak functions are not translated into another language automatically.

    Create a table of holidays, for example:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>1</td><td>Date</td><td>Holiday</td><td align=center>2</td><td align=right>01/01/2004</td><td>New Year's Day</td><td align=center>3</td><td align=right>11/04/2004</td><td>Easter Monday</td><td align=center>4</td><td align=right>01/05/2004</td><td>Labour Day</td><td align=center>5</td><td align=right>20/05/2004</td><td>Ascension Day</td><td align=center>6</td><td align=right>25/12/2004</td><td>Christmas Day</td></table>
    This table may be in another worksheet. Use Insert | Name | Define to give the first column of this table the name Holidays.

    Go back to the worksheet with the date. Say that your date is in D1. In another cell, say in E1, enter this formula, replacing WORKDAY by the Italian version:

    =WORKDAY(D1-1,1,Holidays)

    Now select D1, and select Data | Validation...
    In the dropdown list, select Custom, and in the Formula box, enter

    =(D1=E1)

    In the Error Message tab, enter the appropriate warning.

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    Italian for WORKDAY function is GIORNO.LAVORATIVO

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    Thanks! Sal should be able to get it working now.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    ... WIZARD HansV you know my level of knoledgment ...
    Is possible to attache a simple file in this post.?
    Tks..

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    I have used Tony55's function name; I hope it will work.

    You must have installed the Analysis Toolpak in Tools | Add-Ins... (Strumenti | Componenti aggiuntivi...), otherwise the GIORNO.LAVORATIVO function won't work.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    i have open your wbook but
    IN E1 i have this error #NOME?

    i have already instlled this ad in
    Analysis Toolpak in Tools

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    Try this: select E1, press F2, then press Enter without changing anything.

    If that doesn't work, try to find out what the exact name of the WORKDAY function in Italian is (GIORNO.LAVORATIVO according to Tony55); perhaps I made a typing error.

    As I explained before, the Analysis Toolpak functions are not translated automatically, so I cannot attach the version that works for me. The Dutch version of the WORKDAY function would not work in Italian.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    Perfect!!!!!!!!!!!!

    with this: Try this: select E1, press F2, then press Enter without changing anything.

    only you....

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    HansV, have you experince in importing a text file?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    If you have a question about importing text files, please start a new thread and try to provide details of what you want.

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    Hi, HansV

    but it possible to make a routine in VBA for Excel to get tha same result of the formulas?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    Sorry, I don't understand your question. What exactly do you want VBA to do?

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    ................

    You can use Validation for this, together with the WORKDAY function from the Analysis Toolpak add-in. Unfortunately, I don't know what this function is called in Italian, and it won't help if I attach a workbook, for Analysis Toolpak functions are not translated into another language automatically.

    Create a table of holidays, for example:

    A B
    1 Date Holiday
    2 01/01/2004 New Year's Day
    3 11/04/2004 Easter Monday
    4 01/05/2004 Labour Day
    5 20/05/2004 Ascension Day
    6 25/12/2004 Christmas Day

    This table may be in another worksheet. Use Insert | Name | Define to give the first column of this table the name Holidays.

    Go back to the worksheet with the date. Say that your date is in D1. In another cell, say in E1, enter this formula, replacing WORKDAY by the Italian version:

    =WORKDAY(D1-1,1,Holidays)

    Now select D1, and select Data | Validation...
    In the dropdown list, select Custom, and in the Formula box, enter

    =(D1=E1)

    In the Error Message tab, enter the appropriate warning.

    .........

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SURE INSERTING A WORKING DATE (2000 sr 1)

    But why do you want to use VBA if you have a working solution?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •