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?
    – pjcarly
    Apr 4 '14 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?
    – LoveLace
    Apr 4 '14 at 9:34
  • What is the desired outcome of your formula? Can you give an example of that? Apr 4 '14 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 ..
    – LoveLace
    Apr 4 '14 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

I hope this helps. If you have any additional information/question, add them as comments and we can further improve the answer.

  • This is a good idea! so I'm gonna calculate my days fields according to those two picklists.. that's mean I have to developpe a trigger for that right?
    – LoveLace
    Apr 4 '14 at 15:56
  • You shouldn't need a trigger. I'll update my answer with some more info.. Apr 4 '14 at 18:46
  • Thank's for your answer! the link you gave me helped me to retreive the weekend days from my time off (and i'm looking for retreiving holidays too) I created two picklists (start hour 8:00 - 12:30 / end hour 13:30 - 17:30) that's why i suggested a trigger to manipulate unliked conditions, for example if someone choose start hour: 12:30 end hour: 13:30 it doesnt make sense you see!
    – LoveLace
    Apr 9 '14 at 13:04
  • It's not working !!! CASE(ISPICKVAL(Start_time__c, VALUE("08:00")&& ISPICKVAL(End_time__c, VALUE("12:30") , Time__c= 0.5, ISPICKVAL(Start_time__c, VALUE("13:30")&& ISPICKVAL(End_time__c, VALUE("17:30") , Time__c= 0.5, ISPICKVAL(Start_time__c, VALUE("08:00")&& ISPICKVAL(End_time__c, VALUE("13:30") , Time__c= 0.5, ISPICKVAL(Start_time__c, VALUE("12:30")&& ISPICKVAL(End_time__c, VALUE("13:30") , Time__c= 0.0, ISPICKVAL(Start_time__c, VALUE("08:00")&& ISPICKVAL(End_time__c, VALUE("17:30") , Time__c= 1.0 )
    – LoveLace
    Apr 9 '14 at 16:07
  • That's not the right CASE syntax. I don't think CASE would be optimal to use for this scenario. You could also do it with IF statements: IF(TEXT(Start_time__c)="08:00" && TEXT(End_time__c) = "12:30", 0.5, IF(TEXT(Start_time__c)="13:30" && TEXT(End_time__c)="17:30" , 0.5, IF(TEXT(Start_time__c)="08:00"&& TEXT(End_time__c)="13:30", 0.5, IF(TEXT(Start_time__c)="12:30" && TEXT(End_time__c)="13:30", 0.0, IF(TEXT(Start_time__c)="08:00" && TEXT(End_time__c)="17:30" , 1.0,0 ))))) Apr 10 '14 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.