How to calculate a workday in survey 123

03-22-2019 03:31 PM
New Contributor III
5 2 1,660

Hi! I would like to share a little solution that I made on my own, to help other people and to receive commentaries about it.

In our project sometimes the people will fill a survey in a holiday (in Colombia we have many), at Sunday or after work hours; but we needed to calculate the next workday to register the exact day that the office received the information. Therefore I had to make some calculations to add a day if the hour was after 4 pm, or add # days when we have holidays or if it Sunday, but also I had to make double-check to see if the result of the calculation was another holiday or a Sunday, and add more days. Part of the inspiration came from a response by James Tedrick‌ in the question Weekday Date Calculation

I will show you three examples (in a world where the office does not open at Saturdays nor Sundays:

I won't display the sheet because too long is, but it is attached - feel free to use it

1) today is Wednesday at 4:17pm

2) today is Friday at 4:23pm and we have holiday on Monday

3) today is Wednesday 17 of April at 4:21pm and in Colombia 18 and 19 are holidays

To make it, I made some formulas that I am going to upload and I had a cvs. with the holidays of Colombia (so I used pull data).

Now I would like to make these calculations better. For example, I do not want to have 20 lines of code, maybe there is a way to make it shorter. And Second, I had to repeat the formulas to make this double-check to prevent us to have as a final date a holiday, but I would know if there is a way to make it more beautiful.

Have a nice weekend!

Maybe there was already a solution to this problem and I only lost 2 days of my life, I hope no 😕