Making Daily Sales Planner recognize which month and which week to pull sales data from

I am attempting to make a daily sales planner with my limited excel knowledge. Bare with me if this doesn't make sense. I have also typed some information into the attachment as to what it is I am trying to accomplish. I need help on two parts:

1.) I need the sales planner to reconginze from the date I enter which months tab to pull sales data from, if I enter 1/1 pull from Janurarys tab, if I enter 7/1 pull from Julys tab.

2.) My business weeks run form Monday - Sunday, if the week runs from Monday - Sunday and those days fall within the same month I'm ok, say July 9th (Monday) - July 13 (Sunday). But the first and last weeks of the month have days outside of the current month. The first week of July started on a tuesday, I need a formula that recognizes this and that would give me sales data from June 31 (Monday) - July 6th (Sunday). If todays date is 7/04 give me the sum of sales from 7/01-7/03 from one tab but also add sales from 6/30 from another tab.


I hope this shows you all what I am trying to accomplish. Thanks for any help. Daily Planner.xlsx

Try the attached file.

I added some cells to help with the formula I put in G3.

this woked great but is there a way to leave G3 blank if no date is entered instead of #NUM!. Im just being nitpicky now.

Formula:

Please Login or Register  to view this content.