Extracting information between two dates

[rallysport096]

I researched this and found a formula I believe should work, but for whatever reason, it's not... What am I missing?

I have a list of my employees on Sheet2, and each subsequent sheet has the data for the hours for each employee (Sheet3 will be employee A, Sheet4 for employee B). I'm importing this from Quickbooks Online, so each employee has to have their own Worksheet with their hours.

For the employee worksheets, the hours are in G:G, and the dates are in B:B for each employee. On the summary sheet (Sheet2), the date range is B1 for the start date and C1 for the end date. I'm working with cell C2 to give me the result of hours worked between the date range. I used this formula that I found:

=SUMIFS(Hours,Date,">="&B$1,Date,"<="&C$1)

I made ranges for this as this is how I found it on another site, but I'd rather steer away from ranges since I'm sure we'll hire more employees as we go and I want to make THAT process easier.

Anyway, when I use this formula, the result I get is 0.00. The best I can figure is that the hours are not tied to the date, so Excel returns the 0.00 result. How can I make this work?

Thanks!

 

[mole999]

I would never have guessed

> =SUMIFS(Hours,Date,">="&B$1,Date,"<="&C$1)

represented

=IF($F$2="Custom",SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&N$2,'Employee Name'!J:J,"<="&O$2),SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&H$2,'Employee Name'!J:J,"<="&I$2))

looking at this side by side not having a test rig

=IF($F$2="Custom",SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&--N$2,'Employee Name'!J:J,"<="&O$2),SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&H$2,'Employee Name'!J:J,"<="&I$2))

 

[MARK858]

@mole999, it isn't cell N2 (or O2) that had/has the dodgy dates it was/is the range Employee Name'!J:J (previously named range Date in the original formula).

 

[rallysport096]

I would never have guessed

> =SUMIFS(Hours,Date,">="&B$1,Date,"<="&C$1)

represented

=IF($F$2="Custom",SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&N$2,'Employee Name'!J:J,"<="&O$2),SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&H$2,'Employee Name'!J:J,"<="&I$2))

looking at this side by side not having a test rig

=IF($F$2="Custom",SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&--N$2,'Employee Name'!J:J,"<="&O$2),SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&H$2,'Employee Name'!J:J,"<="&I$2))

Yeah, the J:J is what I'm using for now to avoid using the dodgy date column, as @MARK858 points out. The actual data is on each worksheet (each worksheet is tied to an employee) under column B (or B:B).

I started using the sheet name instead of range names because I wanted the formula in the summary sheet to use a cell for the sheet name so when I need to add new employees that I wouldn't necessarily have to rewrite the formula for each employee or create new ranges each time, or delete an employee and have unused range names (having to worry about deleting the range names), or coming up with new/different names if employees share initials. Originally, I was naming my ranges for each employee "JSHours" for John Smith's hours and "JSDate" for date columns. If I get another employee name Jack Smith, then I have to get creative. I'd rather just have the 'Sheet1'! reference be from a cell on the summary sheet.