SURE INSERTING A WORKING DATE (2000 sr 1)

Topic

New Reply

.. 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?

Reply | Quote

Replies

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.

Reply | Quote

Italian for WORKDAY function is GIORNO.LAVORATIVO

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

i have already instlled this ad in
Analysis Toolpak in Tools

Reply | Quote

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.

Reply | Quote

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

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

only you….

Reply | Quote

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

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

only you….

Reply | Quote

HansV, have you experince in importing a text file?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

HansV, have you experince in importing a text file?

Reply | Quote

Hi, HansV

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

Reply | Quote

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

Reply | Quote

…………….

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.

………

Reply | Quote

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

Reply | Quote

… to make appear a msg box of VBA and not the default msg box from a conditional message

Reply | Quote

The third tab in the Data Validation dialog allows you to specify the icon, the title and the text of the error message, just like you can with a VBA MsgBox.

If you really want to use VBA, you can use the Worksheet_Change event of Foglio1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“D1”)) Is Nothing Then
If Weekday(Range(“D1”)) = vbSaturday Or _
Weekday(Range(“D1”)) = vbSunday Then
MsgBox “You entered a weekend day. Please enter another day.”, vbExclamation, “Attenzione”
ElseIf Not Worksheets(“Foglio2”).Range(“Holidays”).Find(Range(“D1”)) Is Nothing Then
MsgBox “You entered a holiday. Please enter another day.”, vbExclamation, “Attenzione”
End If
End If
End Sub

See attached workbook.

Reply | Quote

BIG WORK!!!!!!!!!!!!!!!!
… but if i insert 01/01/2004 this date is accepeted (?!)

Reply | Quote

Strange.

Reply | Quote

Hum…..
test this with my same steep…
Dx mouse >save as object>c:>open in excel>put in D1 01/01/2004 >enter>nothing msg

Reply | Quote

From your screenshot, it appears that you have opened the spreadsheet in your browser. What happens if you download it to your PC, and open the downloaded version?

By the way, do you get the usual warning that the document contains macros?

Reply | Quote

…. i have this result also i save in C: and open your file from this
My setting macro is in default mode low:

only with 01/01/2004 with other date in the range sheet2 column A work fine

Reply | Quote

I created the workbook in Excel 2002. I don’t think I used anything that is specific to Excel 2002, but I don’t have Excel 2000 to test. Perhaps a Lounger using Excel 2000 would be so kind to test the workbook on his/her system.

By the way, I recommend setting macro security to Medium (Media in Italian). The macro warnings may seem a nuisance, but better be safe than sorry.

Reply | Quote

Tks for suggestion: I recommend setting macro security to Medium (Media in Italian)…
Attending the result from another user that have a excel ver. 2000

Reply | Quote

Tks for suggestion: I recommend setting macro security to Medium (Media in Italian)…
Attending the result from another user that have a excel ver. 2000

Reply | Quote

Your code does not seem to work on XL2K. The modification below works on my system:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D1")) Is Nothing Then
    If Weekday(Range("D1")) = vbSaturday Or _
       Weekday(Range("D1")) = vbSunday Then
      MsgBox "You entered a weekend day. Please enter another day.", vbExclamation, "Attenzione"
    ElseIf Not Worksheets("Foglio2").Range("Holidays").Find(Worksheets("Foglio1").Range("D1").Value) Is Nothing Then
      MsgBox "You entered a holiday. Please enter another day.", vbExclamation, "Attenzione"
    End If
  End If
End Sub

Reply | Quote

Legare,

Thanks for testing and for providing a solution.

You gotta love these subtle differences between versions.

Reply | Quote

tkS. HansV & Legare, it work fine!!!!

Reply | Quote

tkS. HansV & Legare, it work fine!!!!

Reply | Quote

Legare,

Thanks for testing and for providing a solution.

You gotta love these subtle differences between versions.

Reply | Quote

Your code does not seem to work on XL2K. The modification below works on my system:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D1")) Is Nothing Then
    If Weekday(Range("D1")) = vbSaturday Or _
       Weekday(Range("D1")) = vbSunday Then
      MsgBox "You entered a weekend day. Please enter another day.", vbExclamation, "Attenzione"
    ElseIf Not Worksheets("Foglio2").Range("Holidays").Find(Worksheets("Foglio1").Range("D1").Value) Is Nothing Then
      MsgBox "You entered a holiday. Please enter another day.", vbExclamation, "Attenzione"
    End If
  End If
End Sub

Reply | Quote

I created the workbook in Excel 2002. I don’t think I used anything that is specific to Excel 2002, but I don’t have Excel 2000 to test. Perhaps a Lounger using Excel 2000 would be so kind to test the workbook on his/her system.

By the way, I recommend setting macro security to Medium (Media in Italian). The macro warnings may seem a nuisance, but better be safe than sorry.

Reply | Quote

…. i have this result also i save in C: and open your file from this
My setting macro is in default mode low:

only with 01/01/2004 with other date in the range sheet2 column A work fine

Reply | Quote

From your screenshot, it appears that you have opened the spreadsheet in your browser. What happens if you download it to your PC, and open the downloaded version?

By the way, do you get the usual warning that the document contains macros?

Reply | Quote

Hum…..
test this with my same steep…
Dx mouse >save as object>c:>open in excel>put in D1 01/01/2004 >enter>nothing msg

Reply | Quote

Strange.

Reply | Quote

BIG WORK!!!!!!!!!!!!!!!!
… but if i insert 01/01/2004 this date is accepeted (?!)

Reply | Quote

The third tab in the Data Validation dialog allows you to specify the icon, the title and the text of the error message, just like you can with a VBA MsgBox.

If you really want to use VBA, you can use the Worksheet_Change event of Foglio1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“D1”)) Is Nothing Then
If Weekday(Range(“D1”)) = vbSaturday Or _
Weekday(Range(“D1”)) = vbSunday Then
MsgBox “You entered a weekend day. Please enter another day.”, vbExclamation, “Attenzione”
ElseIf Not Worksheets(“Foglio2”).Range(“Holidays”).Find(Range(“D1”)) Is Nothing Then
MsgBox “You entered a holiday. Please enter another day.”, vbExclamation, “Attenzione”
End If
End If
End Sub

See attached workbook.

Reply | Quote

… to make appear a msg box of VBA and not the default msg box from a conditional message

Reply | Quote

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

Reply | Quote

…………….

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.

………

Reply | Quote

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

Reply | Quote

Hi, HansV

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

Reply | Quote

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.

Reply | Quote

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

i have already instlled this ad in
Analysis Toolpak in Tools

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Italian for WORKDAY function is GIORNO.LAVORATIVO

Reply | Quote

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.

Reply | Quote