+ Start a Discussion
CNimmaCNimma 

restrict value after decimal

Hi,

 

Is there any way to restrict values after the decimal place? I have a time tracking field and I would like to restrict the minutes to 60 (Ex: 1.60 and should not be able to enter more than 60 after the decimal). Also, I need to sum the total hours of a week which should convert 60 mins as 1 hour in the "Total" field which is a formula field.

 

Many thanks!

CNimma

Jeff MayJeff May

Not sure I understand your example.  Is your field a decimal field and you want 1.60 to mean 2 minutes?

CNimmaCNimma
Sorry for not being clear. I would like to restrict the values after the decimal to 59 and in the Total Time field, I would like to convert the time into hours.mins (Ex: 2.62 should be converted to 3.02)

Hope I am clear this time. Please let me know if I am not clear.
Jeff MayJeff May

got it.  

 

Here are some ideas:

 

Use a validation rule to check that the decimal is less than 60:  (remember the VR is true if it fails

 

(field -  FLOOR(field) > 60

 

Then for the Total TIme:

 

FLOOR(field) +  FLOOR((field -  FLOOR(field))/60) + (field -  FLOOR(field))

 

You might need to check my parens.

 

 

 

 

 

 

 

CNimmaCNimma
Thanks Jeff but it did not work for decimals. I am still able to enter the values like 1.61, 1.65, 1.70 etc. in the 'hours' field. I basically wanted to create few fields for tracking number of hours (Monday, Tuesday etc.) and calculate the total in the Total field.
Jeff MayJeff May

Here you go:  I forgot to multiply by 100 to get whole numbers. You'll need to do the same for the TotalHours calc in the section where you are dividing by 60 since the result of the subtraction is .60 not 60

 

(( test__c  -  FLOOR(test__c)) *100) > 60

 

CNimmaCNimma

Perfect!! Thanks a lot Jeff. The decimal restriction worked absolutely how I wanted but I am still having trouble with the total time calculation. I have time filled out for Monday, Tuesday etc. and want to calculate the total hours for the week in the Total field. I need to calculate the total time like 1.55+1.20=3.15. Any idea how I can do this?

Jeff MayJeff May

Where is the Total field stored? 

CNimmaCNimma
Total field is stored in the same object. It is a formula field with 'Number' as the return.
Jeff MayJeff May

Since a formula field value is not stored with the record, you can't have it reference itself.  You'll have to have another field and update it with the new elapsed time each time the record changes appropriately.  Be careful not to update it on every record update since some edits may not be collecting new time info and you don't want to double count.  A WF rule and field update might help you out:  total_field = total_field + new_time.