The following formula will calculate the number of working days (inclusive) between 2 dates. A working day is defined as Monday to Friday. Even if the start or end dates are a weekend, these are accommodated.

IF(AND((5 - (CASE(MOD( Start_Date__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  -   Start_Date__c ) + 1) < 7)),
((CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) - (5 - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  -  Start_Date__c ) - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))

The Start Date and End Date fields are custom in the above example and can be replaced as required. If use of a DateTime field is required then the DATEVALUE function will be required.

I also recommend a simple field validation rule is added to check that the End Date is after the Start Date.

Could someone help with creating formula for autopolating contact relaing to a specific name?  I have created a drop down list of names. I would like that every time I enter in one of the names his/her address automatically populate in the address field.

 

thank you!

teacup13 

Hello-

 

Is there a way that I can transfer existing data currently stored in a field to another tab by recreating duplicate field? In another word, I have created 2 tabs that are linked to each other with different fields. After inputing some data, I now realize that I actually would like 1 single tab instead of 2. The problem is I have 77 records saved in the both tabs. I don't know how to transfer these data over, except by manual copying & pasting...

 

Please help!

 

Thanks,

Teacup13

Hello-

 

Can someone pls help with building a formula for calculating the earliest of 2 dates?

 

It works like this:

1. Field A: (8/30/09)
2. 2 date fields but either one could be earlier than the other.

• Field B: (8/31/09)
• Field C: (9/2/09)

 

So i need to calculate the # of business days bt field A and field B (since it's the earlier date). However, either field B or C could change to be earlier than the other. I need to calculate the # of business days bt the earlest field.

 

Thank you!

TC13 

hello-

 

please help with building a formula for SSN. 

 

TY!

Can someone please help me with formula for counting weekdays in a month/ year? In another word, calculating the number of days bt start date and end date in a week/ month/ year. -Thx!

Hello-

 

Is there a way that I can transfer existing data currently stored in a field to another tab by recreating duplicate field? In another word, I have created 2 tabs that are linked to each other with different fields. After inputing some data, I now realize that I actually would like 1 single tab instead of 2. The problem is I have 77 records saved in the both tabs. I don't know how to transfer these data over, except by manual copying & pasting...

 

Please help!

 

Thanks,

Teacup13

Can someone please help me with formula for counting weekdays in a month/ year? In another word, calculating the number of days bt start date and end date in a week/ month/ year. -Thx!

I would like to create a custom field that always displays the last day of the month of the close date. Any ideas on how to create this formula?

The following formula will calculate the number of working days (inclusive) between 2 dates. A working day is defined as Monday to Friday. Even if the start or end dates are a weekend, these are accommodated.

IF(AND((5 - (CASE(MOD( Start_Date__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  -   Start_Date__c ) + 1) < 7)),
((CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) - (5 - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  -  Start_Date__c ) - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))

The Start Date and End Date fields are custom in the above example and can be replaced as required. If use of a DateTime field is required then the DATEVALUE function will be required.

I also recommend a simple field validation rule is added to check that the End Date is after the Start Date.