Calculate X business day of the month

[former_member636715]

I need to calculate 7 business days before the end of the month.. how to count 7 days without including Sat/Sundays?

example.. October would be 10/23

thanks!

[ayman_salem · Accepted solution]

or as follow:

CurDate: =CurrentDate()

lastDayOfMonth: =LastDayOfMonth([CurDate])

DayNrOfWeek: =DayNumberOfWeek([lastDayOfMonth])

DateX: =(7+[DayNrOfWeek]-2)

DDD: =RelativeDate([lastDayOfMonth];-[DateX]+1)

[saurabh_sonawane · Accepted solution]

Hi this formula will fail when the end date will come on Sunday or Saturday plz check.

Plz validate the logic for Saturday and Sunday.

Thanks.

[former_member636715]

I like both answers! lol flipping coin for the winner...

GG Ayman! lol

[saurabh_sonawane]

Hi Geoff

the above correct answer logic fails for Monday also.

I had tested for sep month

[saurabh_sonawane]

Hi,

Hope this formula will help you

=If(DayNumberOfWeek(LastDayOfMonth(CurrentDate())) InList(1;7);RelativeDate(LastDayOfMonth(CurrentDate());-10);If(DayNumberOfWeek(LastDayOfMonth(CurrentDate()))=6;RelativeDate(LastDayOfMonth(CurrentDate());-9) ;RelativeDate(LastDayOfMonth(CurrentDate());-8)))

Thanks,