Calculating days between two dates excluding Week-...

AlexChe · 02-27-2023 06:50 AM

I would like to calculate the number of days between two dates, excluding the weekends.

I have a humble formula that calculates the number of days as such in a Number type column called DaysTaken as such: HOUR([to]-[from])/24.

Once I get this done I think I will create a new sheet in the spreadsheet with a list of dates PublicHolidays and will not count those if they are in the array between [From] and [To].

I am mostly clueless, if you need more info do not hesitate to comment.

Thank you.

Swoopy

My idea is

(All dates count) - (Weekend count)

AlexChe

So have a column calculating the number of dates DaysTaken: HOUR([To]-[From])/24, then have a column calculating the number of weekends days between [From] and [To] CalcWE, and then a third column ResultCalc, making the substraction between the values in DaysTaken minus CalcWE?

Swoopy

or you include the 2 calculations in 1 virtual column, to reduce app's overhead unless you need them elsewhere.

Steve

WORKDAY() - AppSheet Help

Maybe this?

(WORKDAY([start date], ([finish date] - [start date])) - [finish date])

AlexChe

Hi Steve,

As I am calculating a number of days I would need to somehow parse it into a Decimal Type.

But unsure if I should maybe create a virtual column to get the value of this Formula you pasted and then make another calculation with From.

Steve

@AlexChe wrote:

As I am calculating a number of days I would need to somehow parse it into a Decimal Type.

So you might have partial days? That's not what you said originally:

@AlexChe wrote:

I have a humble formula that calculates the number of days as such in a Number type

@AlexChe wrote:

But unsure if I should maybe create a virtual column to get the value of this Formula you pasted

So you haven't tried it yet? Why do I even bother?

@AlexChe wrote:

and then make another calculation with From.

I don't understand this at all.

AlexChe

Mistakes may happen sorry if I am not at your level of perfection m8.

Calculating days between two dates excluding Week-ends