I have a sheet that is linked to a google form so when a person submits the form, the information is populated into the sheet automatically with a timestamp, ex.1/17/2020 17:26:16. I'm trying to sum information based on multiple criteria and one is to only pull a full days worth of data but the formula is reading time as well and so I keep yielding 0.
For example, here is some data
1/8/2020 17:38:49 Danny PM Beetlejuice on Broadway 1144
1/8/2020 17:38:49 Danny PM Oklahoma! on Broadway 1181
1/8/2020 17:38:49 Danny PM Oklahoma! on Broadway 1000.5
1/8/2020 12:47:18 Jeff PM To Kill a Mockingbird 1675
1/8/2020 12:48:19 Jeff PM Jagged Little Pill 2390
On another tab I'm trying to calculate how much was spent by each person on this day. This new tab is looking at a persons shift and name to sumifs their spend:
=SUMIFS('Form Responses 1'!$E:$E,'Form Responses 1'!$D:$D,B$5,'Form Responses 1'!$B:$B,$A9,'Form Responses 1'!$C:$C,$B$2)
I don't believe you'll need to know what each piece in this current code means since I just need to add to it for it to read a range of dates and narrow down to one day.
I've tried adding the date range 'Form Responses 1'!$E:$E and having the criterion be the desired date filled in B2 but this is when it is reading for an exact match of the time from the range which is not going to work since I don't want it to read the time. I want to find a solution that won't involve having to manually update the submission data each time.
I've included a sample sheet here so whoever wants to try and tackle this can better see what it is I'm working with. In the review tab I have my current formula not specifying date and next to it the same but trying to specify the date.
Thank you in advance. My brain is a scattered mess so I hope everything makes sense.
If you want all records for the specified day to be included, you must use the >= and <= operators.
Something like this:
=SUMIFS('Form Responses 1'!$E:$E,
'Form Responses 1'!$B:$B, $A6,
'Form Responses 1'!$C:$C, $B$2,
'Form Responses 1'!$A:$A, ">="&$B$1,
'Form Responses 1'!$A:$A, "<="&$B$1+1)