Thread: Using spreadsheet as calendar and placing last date on different spreadsheet

Hello,

I need to figure out a formula that will pull the most recent date from a calendar spreadsheet that I am using and place it on a different spreadsheet. If you look at the file I have attached you will see the calendar on the "Employee1" sheet. I need the most recent date that has hours in it (January 14 in this example) to show in B2 on Sheet1. The file I have attached is an example and I would use the formula on a much larger file with many calendars. I just need the core formula. Thank you.

If you make sure that your sheet names in column A are exactly the same as the names of the other sheets in the book (in your example file A1 says "Open 1", with a space, whereas the sheet is called "Open1", no space) then in B1 you can use the formula:

=MAX(IF(INDIRECT("'" & A1 & "'!B2:AF13")<>"",DATE(YEAR(NOW()),ROW(INDIRECT("'" & A1 & "'!B2:AF13"))-1,COLUMN(INDIRECT("'" & A1 & "'!B2:AF13"))-1)))

This is an array formula and must be entered with Ctrl-Shift-Enter, but it will return the last non-blank date on the sheet named in cell A1 - although you'll have to format the cell to date format.

Ok, I entered the formula and changed the cell format to date. Now, when I attempt to plug hours into the calendar it changes it to a date and gives me a 5 digit number on the first sheet that makes no sense. Check it out on my attachment. I tried entering 5 (for 5 hours of work) on January 1 and it changed it to 1/5/1900 and displays 40909 on the first sheet.Book2.xlsx

Sorry, you've formatted the wrong column as a date. It's the cell that contains the formula that needs to be formatted to date format - 40909 is the numeric value of the date 1/1/12, whereas the number 5 corresponds to the date 1/5/1900.