How to generate a report that displays an alert or flag beside users if they exceed a specific hour limit?

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)

)
)