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

how is it done?

using excel 365



thank you

Attached Files

BLANK TOTALS FINAL VERSION.xlsx (175.2 KB, 17 views)

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.

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

thank you for the responses

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..

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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

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

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

does that mean this template can't be changed

crossposted: remove weekends

__________________
Alan עַם יִשְׂרָאֵל חַ Using O365 v2505

Looking at your example workbook (post #1), I'd advice an data entry sheet where all needed data will be entered as table or Defined Table, with columns like:
Date; School; Employee; WhateverYouEnterForWeekdays
and probably additional calculated columns like:
Position; Month (I advice to use format like 2022.09), and optionally [WeekdayNo]; [DayNo]
To get Position for Employee, you also need am Employees table, with columns for employee and it's Position (in case the position of employee can change over time, you must have instead Employees table, an EmployeePositions table with columns for Employee, Position, and PositionDate, and probably a hidden sheet with table, where current position for every employee is listed)
You can also add a calendary sheet, and a holidays sheet (or have a column in calendary sheet for holidays) - to use in calculations (e.g. to determine, is the date workday or not).

And then you can create a Report sheet, where user selects School and MonthNo, and the table like in School sheets of your example workbook is filled with data from data entry sheet.

As result, your workbook will have sheets for data entry, for Schools registry (your Top table), for Employees registr(y/ies), and a single report sheet. To add new schools in future, you simply have to add them into Schools registry, and you can start to enter any needed data for new school into data entry table.

Can the formulas in Row 6 can be changed so that it will display only weekdays of that month only instead of unwanted dates before the month and weekends.