3

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 ?

4
  • Does your formula field have decimal places?
    – pjcarly
    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?
    – LoveLace
    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 ..
    – LoveLace
    Apr 4, 2014 at 10:09

1 Answer 1

1

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.

10
  • 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, 2014 at 15:56
  • You shouldn't need a trigger. I'll update my answer with some more info.. Apr 4, 2014 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, 2014 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, 2014 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, 2014 at 7:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .