compare dates by macro?

[smiler44]

I have two variables containing dates in the format dd/mm/yy
I want to compare them to each other and find out how many working days there are between them. A working day is Monday to Friday
I need to know if the dates are more then 30 working days apart.
If they are more then 30 working days apart, I need to know by how many and then add those number of days that are over 30 to the first date to give me a new working date.
what should I declare my variables as? Dates?

for example
04/02/14
25/03/14

this is 35 working days apart and so my new date is 11/02/14

I need to do this my a macro but how?
thank you
smiler44

 

[namliam]

30 workdays in your definition is the same as 42 calander days, why not work with that?

Yes always declare varaibles in the type you are going to be using the values as, in this case dates.

 

[smiler44]

namliam, thank you for such a quick reply. I don't know the answer to that
please could you give me some code I can try?
from my first date/variable, I will need to work out 8, 14 and 30 working days. If the date in my second variable is more then 30 working days I nee to know by how many and add that to the 8 and 14. so in my example I will need to work out 13 and 19 working days.

right now I cant work out how to do it

smiler44

 

[Brianwarnock]

To calculate the number of working days between two dates you use
NETWORKDAYS in Excel 2007 and earlier
And
NETWORKDAYS.INTL in 2010 or 2013

Both allow for holidays as an option, if you are including holidays in your calculations then the second part of the problem becomes more complex, if not then I think a simple algorithm can be devised to cope with the 1to 4 days scenarios, 5 days as per your and Namliam's examples is obviously straightforward
.
So do you wish to allow for holidays, if not I will write and test the code.

Brian

 

[Brianwarnock]

As I slowly became more awake I remembered that NETWORKDAYS is not a VBA. Supported function, plus we have been here before, May 2013 to be precise.

Brian

 

[Brianwarnock]

ok breakfast over and thinking cap on, NETWORKDAYS and WORKDAYS both work in 2013 so with 2 simple to use worksheet functions your calculation is done, holidays included if you wish, interestingly NETWORKDAYS returned 36 for your example, thus it includes both dates in its count, correct for how many working days in the date range, if you say between should you include either date?

brian

 

[Brianwarnock]

Starting to find my way around 2013 help, found the list of supported worksheet functions in vba and both NETWORKDAYS and WORKDAY are there, so might be there in 2010 also.

Brian

 

[smiler44]

Brian,
thank you. I'm just coming back to the post to say I think I have solved this. I have excel 2007. As I say I think I have not yet confirmed as I have some more playing around to do but it was looking good last night.
from memory of last night I have used a combinations of macros and formulas.
Macro1 gets the 2 dates and pastes them into excel.
formula1 works to a point and is the workday one, for the life of me I cant get it to work at the moment on the pc I'm using now. 27/03/14 and 28/03/14 the formula counts as 2. I want it to count it as 1
Formula2 deducts 1 from the answer that formula1 gives.
I think I then have a macro or formula to add the number of days given in formula2 to 27.03.14
I then have to add 8 working days to this answer.

checking against a calendar it has worked but I need to test it a bit more. I also need to add in bank holidays although I may be able to get away with these as an error, I will have to see.

Once I have proved it works I'll come back and post again.

I think on the pc I'm using now, the formatting of the cells is a problem and cant recall what I formatted the cells as on my laptop :banghead:

thank you for your help but what I thought would be easy is quite hard

smilerr44

 

[Brianwarnock]

Workday is the function that adds the number of days to a startdate NETWORKDAYS is the function to give the number of days in the date range and does as I stated earlier include start and end dates, that is in fact logical.

Brian

 

[smiler44]

Thank you Brian, I've had another go using workday and it now works. I've also included the 2014 bank holidays.

From starting Thursday to being where I am today I have done an excellent job.
I have code to add before I can pass it around to other people. Most of this I already have examples of, 1 other bit other I may have and one bit I will try to find on the internet, otherwise........... I'll be back

smiler44