Uncount Holidays for Leaves

[Axmed.cm]

Hi everyone.
I was planing to create a tool for my staff leaves.
There is weekly day-off for the staff. I have managed to uncount the day-off.
Can I have the holiday uncounted?
Holiday is in cell B1
I attached the file.
Thank you in advance

[Axmed.cm]

Please help

[shknbk2]

Try this file. I modified the code so that the leave days skip days off and holidays. For example, if 4 leaves start on Tuesday and Thursday is a holiday and the day off is Friday, the leave days are Tuesday, Wednesday, Saturday and Sunday. Also, the weekday off can be changed on the worksheet rather than having to change the code every time. Some of the cells have been moved as well.

[Axmed.cm]

Thank You Dear

Its SOLVED, and I have got the point.
Just few more requests:

1. When taking new leave it clears the history. I need to keep the leave history.
2. I am planning to expend it over the entire days of the year. The holidays may be many. I need to have at least 8 different holidays through the year.

3. if possible, can I have the data being exported/imported to/from text file.

thank you again and again..

[shknbk2]

Here is an updated file for answering 1 and 2. For importing to/from a text file, more info is needed. What does the text file look like? Where is the text file saved relative to the Excel file (in the same folder or somewhere else)?

[Axmed.cm]

Hi Dear
Thank you for your time and effort. This is fully helpful.
By referring your codes, I have made some development to the tool as you can see.
I have added a userform for managing leaves.
Although I made no more changes to the codes you shared, but unfortunately it is not working for me, and I don't know why it is not. I hope you can detect the reason.
In terms of exporting/importing..
I need to have a small file with the data to export/import instead of sending the whole file.
I have seen some excels that can be exported/imported to/from text files.
Here I guessed incomplete macro codes that I hope it is possible to do so..
But this is optional (If this is difficult or impossible, I will get rid of it)
My most worries is why the userform is not functioning..
Thank you my dear.
You really did me a favor

[Axmed.cm]

Sorry dear.
this is the updated file. Ignore the first.

[shknbk2]

Replace the Click routine with this code. There are only a few differences. Your code is working well otherwise.

It took me a while to figure out the problem. When searching for the dates in the worksheet, Excel can't find any date (such as 1/1/16) in the format you have with the dates in vertical text because the column width is too small. Even though you have the text showing vertically so the whole text shows ("1/1/16 (Fri)"), this text would not be shown if the text was horizontal. Instead, "####" shows up whenever a number is too long for the cell. Since your column widths are small, Excel is really seeing the # characters rather than the text that you and I can see vertically. The code below changes the column widths to be larger during the search and then changes them back after the routine is done.

The only other change I made was to change the format of the dates searched for the leave dates. Instead of searching for "1/1/2016", the search should be for "1/1/16" to match the Custom format (it isn't necessary to also search for "(Fri)" or whatever if you don't want). There were 2 places I changed the searching to be in this format.

So, 4 lines changed in your code is not bad. The code is fine every other way.

The saving to a text file and reading back is not impossible, but I don't have time to work on it now. Maybe in a few days if you still need it.

Please Login or Register  to view this content.

[Axmed.cm]

Thank you dear.
I have every reason to wait you.

Now it is functioning well.
also I have just detected the problem.
the problem is that the dates are formulated.
When I write it directly then it becomes ok.
That is SOLVED..

For saving as textfile and reading back:
When you get chance, if you send it via mail it would be better.
Email: [email protected]

I will remind you after few days.

Thank you for your time and help