removing weekends

[Nachobear]

Hi can anyone please help me remove the weekends in this file

how is it done?

using excel 365

thank you

[ArviLaanemets]

As I haven't Excel available currently, I can only describe what I would do.

I'd have a Calendary table in separate [hidden] sheet, something like:
Date, ..., WeekdayNo, IsHoliday, DaysAfterPreviousWorkday, DaysBeforeNextWorkday, ...;

All values in this table would be calculated for period determined in column Date. This column contains a list of all dates from start of calendary until end of it;

In column WeekdayNo, the weekday number for Date is calculated (values 1 - 7);

IsHoliday will be either 1 when Date is State Holiday, or 0 when otherwise;

Those 2 columns are used to calculate:
a) DaysAfterPreviousWorkday will be 0 when Date is workday. It will be > 0, when the Date is not workday. When the previous day was workday, it will be 1, when the previous day also wasn't workday, but day before it was, it will be 2, etc.;
b) DaysBeforeNextWorkday will also be 0 when Date is workday. It will be > 0, when the Date is not workday. When the next day will be workday, it will be 1, when the next day also isn't workday, but day after that it will be, it will be 2, etc.;

Now, whenever you calculate a date, and need to ensure that it will be workday, you have to read the value of DaysAfterPreviousWorkday or DaysBeforeNextWorkday from Calendary table, and substract or add this to date. When the date was workday, it remains same. Otherwise the date will be changed for proper number of days. The best formula to do this all will be SUMIFS.

[Nachobear]

HI I'm new to entering formulas, is there a way someone can guide me

thank you for the responses

[Pecoflyer]

What are you actually trying to do? Please describe in detail. Will there be further analysis needed ? If so merged cells are a no-no..

[Nachobear]

Originally Posted by Pecoflyer

What are you actually trying to do? Please describe in detail. Will there be further analysis needed ? If so merged cells are a no-no..

Hi

I just need to remove the weekends and to do that I need to learn some more excel like how to add in the formula SUMIFS, which the other person recommended.

Thank you everyone

[Pecoflyer]

Sorry, can't help with this kind of layout. Good luck with your query