+ Start a Discussion
DeptonDepton 

Age field avoiding weekends.

Hi,

 

I am running crazy trying to calculate age since a custom date/time field gets populated. Avoiding weekends.

I thought I founded the formula but is giving me some errors.

 

Anyone has a formula that counts the hours bases on a date/time field?. Avoiding weekends.

 

So it will count 24 hours from Monday to Friday.

Zero hours Saturday and Sunday!

 

Thanks.

Navatar_DbSupNavatar_DbSup

Hi,


Try the below formula and made changes accordingly:
CASE(
MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)
Now you have total number of working days excluding Saturday and Sunday.

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved. 

DeptonDepton

Hi Navatar,

 

Thank you, I got this one but I wanted to get the hours instead of  number of days.

So 24h from Monday to Friday

Zero for Saturday Sunday.

 

Any other ideas?