Date Formula (Working Days) 1

[strom99]

Is there a way to count the number of working days that have gone by (for example Nov has 20 working days in our division and I need a formula that says 17 of 20 days and then tomorrow it would say 18 of 20 days complete) Is this possible. I am using a formula that Ken Hamady created to count the number of working days. Please advise

Thanks

 

[kenhamady]

Glad that my formula was helpful. The formula uses two input dates, which you can calculate separately to keep things simple.

Sounds like you want the start date to be the first day of the current month, and the other to be today's date?

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[strom99]

Is there a formula that will automatically return the first day of the current month and the last day of the current month? I have tried many ways and cannot get it to work.

Thanks

 

[strom99]

One final question -

There is a 3 day lag from the current day to the day we report sales, so today is billing day 18 of 20 which is reporting data from November 28th. When the month rolls over to a new month how will the formula recognize that I am still reporting in November and that on Dec 6th it will be billing day number 1.

Thanks for all the help

 

[kenhamady]

First day of Current month:

CurrentDate - Day(CurrentDate) + 1


Last day of Current month:

DateAdd('m',1,CurrentDate) -
Day (DateAdd('m',1,CurrentDate))

If you are want the report to use a different date for today, use "Report - Set Print Date". This controls the PrintDate and the CurrentDate functions. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

While on the topic of business days/hours in a range,

http://support.seagatesoftware.com/library/kbase/articles/c2008105.asp

has a link to UFL for calculating business days/hours in a range. For business days, you define what your days are (usually M to F), and create a little text file of your holidays (one line for each holiday date). For business hours, you also define the start and end times of the day.

I've never had to use either this UFL or Ken's version, so don't take this as an endorsement of one or the other. I just happened to stumble on this UFL while looking for something else. Malcolm
wynden@telus.net

 

[strom99]

For anybody who cares, I am using Kens code and it works perfectly.

 

[MalcolmW]

That's more than you can say for some seagate code Malcolm
wynden@telus.net