# Manipulating the date/time field

I have a problem in manipulating my date/time fields, here is the problem:

I have two date/time field for my time_off__c custom object:

Start_date__c, End_date__c and a formula field number type Days__c

at first, i've declared them as a Date type so: Days__c = (End_date__c - Start_dats__c)+1 so Days__c will get the number of days token for the time off

The problem now is if someone asks for 2 days and half (for example) I swtiched to a date/time field to for my Start_date__c, End_date__c but Days__c still a number.

Don't know how to manipulate those half days! any Idea ?

• Does your formula field have decimal places? Apr 4, 2014 at 9:19
• No, didnt specify a decimal places! but is it a solution to this problem to have a one decimal palce? because I guess I will have a problem in days formula field, is that right? Apr 4, 2014 at 9:34
• What is the desired outcome of your formula? Can you give an example of that? Apr 4, 2014 at 9:57
• Asked a time off: Start_date__c : 04/04/2014 08:00 End_date__c: 04/04/2014 12:30 >> Days__c = 0,5 | Start_date__c: 04/04/2014 08:00 End_date__c: 04/04/2014 17:30 >> Days__c = 1,0 | Start_date__c: 04/04/2014 08:00 End_date__c! 05/04/2014 12:30 >> Days__c = 1,5 Etc .. Apr 4, 2014 at 10:09

Substracting the two datetime fields will give you the amount of days between the 2. So if there's 1 day and 12 hours between the datetime values, it will return 1.5 (provided you add a decimal to the formula field).

However, what you want is a number of working days. So in that case, if the difference is e.g. 8 hours, the formula will return 0.3(333333).

If you limit the exact times that can be selected to e.g. start/mid/end of workingdays you could translate the formula outcome to a display value of 0.5, 1.0, 1.5 etc.. However, that's quite tricky and won't support 1 hour off etc.

I'd recommend to create 4 fields: 2 date fields with start/end date and 2 picklist fields with the start/end hour. Then the formula field would first substract the date fields and then add the difference between the (numeric value of) the 2 picklist fields to finally come up with the amount of workingdays off. Like this:

Formula field 1: NumberOfDays is EndDate-Startdate. Formula field 2: NumberOfHours is EndHour-StartHour (can also be negative!) Formula field 3: NumberOfDays+((EndHour-StartHour)/8) (8 is the number of working hours in a day)

You could also combine all of this in 1 formula field.

Alternatively you could use the formula displayed here to calculate the amount of working hours between the datetime fields and derive the number of days from that: http://help.salesforce.com/HTViewSolution?id=000089863&language=en_US