Help: Calculating MTD, QTD, and YTD from daily data input

[misterk]

Greetings!

First post here. I usually can find what I need online through a quick search, but I have come up empty on this so far. This seems like a simple issue, but I cannot generate a formula that works as needed.

Here's what I need:

I have a workbook that currently has 3 sheets/tabs (I have trimmed it down for this forum). Basically, managers need to access this spreadsheet, input their data for the day, save, and close. I have a formula I found online that will display the most recent data input (as long as there are no blank cells in the columns) for the "Daily Report" column. In my monthly report section, I need a couple different calculations to show the MTD numbers. One formula will just be equal to whatever the daily number is (it is a reportable rate), so in E5 I'll have "=C5".

I need a formula for my other MTD calculations that displays the data during the current month and the total year-to-date.

If a manager is inputting data for April 9th, I want my MTD cell on my reporting page to show the accumulated data (sometimes this will be a sum, others might be an average) for April 1-9, and my YTD to show Jan 1 - April 9. Then when it's May 1, the MTD will show May totals, and YTD will show Jan 1 - May 1.

That's the gist of it. I know this might be a little confusing. I didn't realize how difficult it would be to write-out an issue like this. I have posted an example file with the dummy data. The client is a mine in the US and I think I removed all sensitive information (please let me know if I did not).

Thanks for you help!

**Note: On the spreadsheet, in column C, any cell with a "-" will not be inputted daily, but they will still put in data at the end of the week and will need MTD, QTD, and YTD calculations. Prior Year and Benchmarked columns will be hand entered for the time being.

[smb_146]

check the attachment

[misterk]

check the attachment

smb_146,

Thank you for your help!

The MTD and YTD calculations seem to be working. It looks like it is partly calculating off the system Date & Time. Is that correct?

This seems to be the perfect solution.

[smb_146]

Yes dear the calculations are based on system date & time. I did this intentionally so that you don't have to make changes i.e. changing criteria in the functions

[misterk]

Great, thank you again for your help.