Returning Information by Date

Greetings To All Who Will Review This,

I have spent hours researching solutions for the remaining half dozen issues and wish list items I would like to have cleaned up concerning this workbook. Overview, a user enters data into the RO-CT and Daily Database sheets (I have populated June 1-4 to provide a working sample). Then on the Daily Report sheet the user performs one action, enters a date in cell B2 and the entire Daily Report becomes populated by date, pulling and calculating the information from both database sheets.

This brings me to my first two issues:

1 On the Daily Report sheet I cannot figure out the code to populate the CUSTOMER COLLECTIONS and CASH PAID OUTS sections with the notes and dollar amounts which are entered on the Daily Database sheet. The entrie(s) need to also populate by the date entered in cell B2.

2 On the Daily Report sheet I cannot figure out the code to calculate MONTHLY SALES TOTAL AS OF YESTERDAY (E31). While the code works while a particular month is in progress, when I pick a date earlier in the month, the total for the month is being returned instead of only to the day before the date entered in cell B2. I keep screwing up trying to slip a DAY function into the line of code.

Thank you to all who took a look.

Hi John,

Thank you for your reply. The Customer Collections/Amounts works perfect and I was also able to modify your code to get the Cash Paid Outs/Amounts working perfect. Part 2 is working and calculating the math by day correctly, but it is doing so in the opposite direction of the days of the month. Meaning, on 6/1 the total month sales is $12,431 and on 6/4 the total month sales is $400.

I tried to modify your code to get back to the beginning of the month with this

=SUM(IF(('RO-CT Database'!A3:A1201>='Daily Report'!B2)*('RO-CT Database'!A2:A1200<=B2-DAY(B2)+1),'RO-CT Database'!D2:G1200)) Ctrl+Shift+Enter

It worked for 6/1, but then exploded on the other days. Doom on me!

Your thoughts and time are appreciated.

Not sure I understand: If you change B2 to 6/4/2016 then on your supplied data you are only counting 1 day (6/4 is the last date) so the Monthly Total will be for that day only. i,e $400.

=SUM(IF(('RO-CT Database'!A2:A1200>='Daily Report'!B2)*('RO-CT Database'!A2:A1200<=TODAY()-1),'RO-CT Database'!D2:G1200))

this works by taking all data from date in B2 (assumed to be start of a given month) and calculates up to TODAY()-1 ie. yesterday.

So today (6/23/2016) it will sum from 6/01/2016 to 6/22/2016

Isn't that what you want?

Ok, got it. I think part of the confusion is the form itself. It was hand written which is why the month sales as of yesterday line exists, then the current days sales is added and the new total was written on the month sale as of today line. I guess with trying to build this Daily Sheet to work in Excel and not do it by hand any more this double entry is no longer needed.

In reality only the "month sale as of today" would be needed which would need to work like this:

Information entered into the RO-CT Database
Date: Sales:
6/1 2
6/2 2
6/3 2
6/4 2
6/5 2
6/6 2
6/7 2
6/8 2
6/9 2
6/10 2

Date entered into the Daily Sheet
Enter 6/1 in cell B2, month sale as of today (E32) returns $2
Enter 6/4 in cell B2, month sale as of today (E32) returns $8
Enter 6/10 in cell B2, month sale as of today (E32) returns $20

If one should enter April 15, month sale as of today should sum the first half of April (and so on by month).

For all the time and effort I have put into this, my apologies for not critically thinking about how this section could be simplified prior to sending out for assistance.

Thank you again for providing me with a teachable moment.

No problem.

If your question has been addressed, could you please mark the thread as solved ("Thread Tools" at top of first post).

Hi John,

Thank you for continuing to entertain this subject again and I am sorry I am having a difficult time explaining. I hope I have done a better job this time.

Information entered into the RO-CT Database
Date: Sales:
6/1---2
6/2---2
6/3---2
6/4---2
6/5---2
6/6---2
6/7---2
6/8---2
6/9---2
6/10--2

What it should do:
Date entered into the Daily Sheet should return sum of sale moving forward through the month:
Enter 6/1 in cell B2, month sale as of today (E31) returns $2
Enter 6/5 in cell B2, month sale as of today (E31) returns $10
Enter 6/10in cell B2, month sale as of today (E31) returns $20

What it is doing:
But it is returning from today backward to the beginning of the month:
Enter 6/1 in cell B2, month sale as of today (E31) returns $20
Enter 6/5 in cell B2, month sale as of today (E31) returns $10
Enter 6/10in cell B2, month sale as of today (E31) returns $2

I have attached the modified workbook again and removed the yesterday sales line to streamline my mess.

Thank you again.

I explained this previously: B2 is the START date of a month and using TODAY() you get the cumulative from B2 to TODAY().

B2 should only change when the month changes (if you require monthly ) data.

If you want it work by changing B2 use:

=SUM(IF(('RO-CT Database'!A2:A1200>=EOMONTH('Daily Report'!B2,-1)+1)*('RO-CT Database'!A2:A1200<='Daily Report'!B2),'RO-CT Database'!D2:G1200))

this will start from 1st of B2 month until B2

Hi John,

Thank you very much for the explanation and now I truly and completely understand the code. Thank you again for your patience. What I have been failing to effectively communicate is the dollar amount being displayed in E31 on a given date seems to be not being calculated from B2 to TODAY(). It seems to be being calculated from TODAY() to B2.

So when the date of 6/1 is picked the total sales for the month is being displayed, but their has only been one days sales to account for.

Question:
Is there a way to inverse the result being displayed in E31?

Truly what you have helped me with is working, by date and everything and I am truly grateful. Did I just ask the wrong question?

You have completely lost me:

Either you change B2 and it calculates from 1st of the month of B2 until B2

OR

you leave B2 as 1st of month and calculate B2 to TODAY()

the choice is yours and you have been given both formulae

OMG! I am SOOOOO sorry! I started Post #10 as soon as #8 posted. My kid needed me and I got logged out by the time I finished it and went to post it. I never knew post #9 came in from you. No wonder why you must consider me an idiot in Vegas. So a quick copy and paste and Ctrl+Shift+Enter. Looks as if it works perfect!

Thank you, thank you, thank you.

Posts in the night!!! It happens quite often so you're not the first.

If everything is OK could you please mark the thread as solved ("Thread Tools" at top of first post).