Search results

New file

I also need to simplify and use fewer columns to get to the Final January FTE.

Okay here is a specific problem. I added transfers as an additional row in the table. I am trying to calculate the # of days worked in AI15 and AJ15, to EXCLUDE days out of the office on Leaves of Absences. I need to subtract all LOA days in 01/01/2015 through 01/15/2015 from AI15 and...

I am looking for help to set up a spreadsheet to find the FTE for an employee in a particular dept, location, title for each month. Transfers of dept, location, or title can happen any day of any month for 2015. The FTE is the portion of the month in that combination of categories divided by...

New file!

Specifically columns AA, AB and AD.

For example, I would rather use EOM than the Date(2015,1,1) type formulas I am using now

Do you see anything else that is needlessly complex in this file :)

Ah I changed the column to F! It worked!!!

Uploading latest file with your formula. For some reason, instead of returning the value for January FTE from the table it is returning the value in M8 of the first tab.

Ah this might work! Trying it out

Very beginner, how do you name a table?

I am trying to correct column AF in this first tab. If Person A appears in the table on the next page, I want it to return the January value in that row, otherwise I want it to return the beginning of year Standard FTE Column M

Question. Is it possible to set up a conditional like this: If Gloria Smith appears in a table, then use the number from column E row 1 in the table, otherwise use the number from column A in this tab.

Yay it works!

You beat me to it! Thanks!

So right now my formula is: =IF(Q8="",0,(IF(OR($L8>AC$6,$L8=""),(((MAX(0,MIN(EOMONTH(AC$6,0),$R8)-MAX(AC$6,$Q8))))))))+IF(S8="",0,(IF(OR($L8>AC$6,$L8=""),(((MAX(0,MIN(EOMONTH(AC$6,0),$T8)-MAX(AC$6,$S8))))))))+...

I think then it will add too many days when the LOA is only within the month

It should be exclusive because they start back on the date their LOA ends, so the end date should be INCLUDED in days in the office, but in the case where the LOA goes over into the next month, the last day of the month needs to be counted.

I am okay with these formulas although they are a bit complex. However the formula fails when the LOA is for an entire month. It returns 30 for Days Out of Office, instead of 31 days. How do I fix this?

Actually your formula also doesn't work if the LOA is outside of the month of January. It still counts takes away the days out of office in January even if the LOA is in March 8 - March 22nd for example.

Hmm didn't work for multiple LOAs or blank LOAs.

Hi this works. How would I modify it to include all of the LOAs? Just do + and the formula for those cells?

I would like to count the days in the office in a given month, where there is a start date, Leave of Absence date (up to 3-4 leaves of absences), and a termination date. I have attached the file. In Column P I have the formula...