Auto populating

I suggest that you change the format of column A to mmmm d, yyyy and delete column B. Column A will then present the Day as January 2, 2014 and this will work well with a Pivot table.

If you use full dates, you should be able to filter by years, months, days. I do it all the time with my data. Right now when you enter the month and day, you are only entering a partial date. Add the year.

The method that you have of creating your dates is a problem. I copied the data from columns C and D to sheet 2 and re-created the dates normally and also created a Pivot table that could group the dates by year, month, day etc.

I would just copy the data in columns C and D down the page seeing that they will never change. Then all you have is the date entry. You can enter the date in the first cell for the date then select that cell and extent the selection to the end of the day and use Ctrl + Down arrow to fill in the date. Or use Ctrl + ; to enter today's date and copy it down using the above method.

Yeah, that was my original intent; however, long, preexisting columns of data aren't very aesthetically pleasing and I think having the shift and time auto-populate would be favored by the user. I've been fooling around with it for some time now and just can't figure out how to do it!

Also, in regard to your pivot table, how did you get each line to be flush without gaps?! You're a wizard!

Click in the Pivot Table. The Pivot Table tools will appear in the ribbon. Click on the Design tab. At the other end of the ribbon is Subtotals, click on that and click on Do Not Show Subtotals.

It is also easier, I think, to read the Pivot Table if you click on Report Layout and choose Show in Tabular Form.

I will do a little more tinkering around with your example to see if I can get something workable for you.

I have been working with your original data. I changed it to a table and added a column which is now Column C which you can hide if you like. I recorded a macro that will copy a copy of your original setup + the added column and the blank "template" is added to the bottom of your data ready for the date to be entered. Don't delete the data that is in the table just change the date then you should be ready to go. Click the button and the macro will run leaving the cursor in the first available cell for the date to be entered for new data.

This is about all that I can do in this area. Someone conversant in VBA would certainly be able to make the macro better.