Excel FTE calculations help please

Your layout is what we called pivoted data. And this layout is bad for Excel because you can not use all the powerful tools inside to calculate the results.

Anyway, there is a solution with layout like this (random data without any sense, note that you separate names with a comma in a cell):

I do not formatted the data as table because to keep the formulas in row 1. Next year all I need to do is to change the date in B1 to get the full calendar for 2025. (It's just a simple layout, you can add weekends, holidays etc. as usual in other calendars).

The trick is to create a named range over the full calendar, then we can load the data into Power Query and unpivot into this:

This is perfect for analyze, so we can use e.g. a Pivot table and get a result like this:

This is pretty simple and we don't need any formulas at all. Here is a sample file:

https://www.dropbox.com/scl/fi/7uw8a0573su2gj12mr0h6/bc4d7fa9-0507-4a09-87a6-4aaba7dfc136.xlsx?rlkey=2s2huz8kbahrqe9cw91bwtmjt&dl=1

Andreas.

Possibly! I will try shortly, thanks

So the numbers are a decimal percentage of what people work. Across the top of my file will be dates (split into monthly blocks and if someone Is full time I'll put their name in the row 1.0 under the corresponding dates. If someone only works 4 days a week I'll put their name in the 0.8 row and their corresponding dates.

So say both people who work 1.0 and 0.8 have January 1st off, if Both their names (the A reference place holder) are under that date I want the total to automatically calculate that 1.8 people have booked time off.

Hi,

How have you calculated the numbers in the FTE column? What do you mean by "I need it to be worth 1 or 0.8 or 0.1"? Share data in a format that can be pasted in an MS Excel file.