function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Chris EChris E 

Formula field: Is any date within range a weekend?

My company deals with bookings that store check-in and check-out dates in two fields. I would like to add a custom field to the object that will look at those two dates and see if the range they describe contains a weekend day (not including the Check-out day, on which guests will not actually stay).
This would then return "WKEND", so that we could easily report on the number of weekend bookings taken. 
Any ideas?
 
Beer NutthawanBeer Nutthawan
You can try this trigger instead:  
Time t = Time.newInstance(0, 0, 0, 0);
    Boolean boolHasWeekend = false;
    
    for(nwp__Signature_user__c record:Trigger.new){
        
        Date dt = record.nwp__Check_in__c;
        Datetime dttmp = DateTime.newInstanceGMT(dt, t);
        
        if(dt == record.nwp__Check_out__c){ 									// Book only 1 day 
            if(dttmp.format('EEE').equalsignoreCase('sun') || dttmp.format('EEE').equalsignoreCase('sat')){ //Check if its Sunday or Saturday or not
                boolHasWeekend = true;
            }
        }else{																// Book more than days
            while(dt < record.nwp__Check_out__c){
            	dttmp = DateTime.newInstanceGMT(dt, t);
           		if(dttmp.format('EEE').equalsignoreCase('sun') || dttmp.format('EEE').equalsignoreCase('sat')){ //Check if its Sunday or Saturday or not
					boolHasWeekend = true;
                	break;
           		}
           		dt = dt.addDays(1);
         	}
        }
        if(boolHasWeekend){
        	record.nwp__isWeekend__c = 'WKEND';
        }else{
            record.nwp__isWeekend__c = '';
        }
    }

 
SteveMo__cSteveMo__c
Give this a try: 
IF(
(End_Date__c - Start_Date__c) >= 
CASE( 
MOD( Start_Date__c - DATE( 1900, 1, 7 ), 7 ), 
0, 7, 
1, 6, 
2, 5, 
3, 4, 
4, 3, 
5, 2, 
1), "Weekend",
NULL)

or you could use a Formula(Checkbox) field and use this 
(End_Date__c - Start_Date__c) >= 
CASE( 
MOD( Start_Date__c - DATE( 1900, 1, 7 ), 7 ), 
0, 7, 
1, 6, 
2, 5, 
3, 4, 
4, 3, 
5, 2, 
1)


 
Chris EChris E
Cheers SteveMo, was struggling with the apex and had dropped off looking at this, so i'll give it another go with the formula
Reid BauerReid Bauer
Thanks SteveMo. 7 years later, your formula still works a treat as a row-level formula.