CUSTOMER ZONE
How to generate a report that displays an alert or flag beside users if they exceed a specific hour limit?
Find resources designed to help you get the most from Replicon
The sample Custom Column formula furnished below helps to find if users have exceeded 8 hours from Monday to Thursday and then to find if they have exceeded 5.5 hours only on Fridays:
if (
(Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))
–
(7*floor((Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))/7))
= 5 && TotalHours > 5.5, "Exceeded Hours on Fridays (5.5 Hours)", if (
(Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))
–
(7*floor((Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))/7))
= 4 ||
(Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))
–
(7*floor((Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))/7))
= 3 ||
(Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))
–
(7*floor((Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))/7))
= 2 ||
(Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))
–
(7*floor((Day(EntryDate)
+floor((13*if(month(entrydate)>2,month(entrydate)-2,month(entrydate)+10)-1)/5)
+if(month(entrydate)>2,mod(year(entrydate),100),mod(year(entrydate),100)-1)
+if(month(entrydate)>2,floor((mod(year(entrydate),100)/4)),floor((mod(year(entrydate),100)-1)/4))
+floor(((year(entrydate)-mod(year(entrydate),100))/100/4))
-(2*(year(entrydate)-mod(year(entrydate),100))/100))/7))
= 1
&& TotalHours > 8, "Exceeded Hours on Weekdays (8 Hours)", NumberToText(0)
)
)
Start a free Replicon trial today based on your business needs
Start Free TrialSign Up For Our Newsletter
