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]

ranges can be modified in name manager, so that it is obvious what you are updating. The issue potentially is that things like hour and date are used by excel, you may do better with bob and tom

 

[MARK858]

@rallysport096, your formula works fine for me. In the employee sheet in the first cell in a spare column can you put the formula

=ISNUMBER(B1)

and drag down then post whether you get TRUE or FALSE.

Edit: I also would change the names you are using for the ranges but it doesn't cause an issue for me with your formula.

 

[rallysport096]

@rallysport096, your formula works fine for me. In the employee sheet in the first cell in a spare column can you put the formula and drag down then post whether you get TRUE or FALSE.

Edit: I also would change the names you are using for the ranges but it doesn't cause an issue for me with your formula.

I only used the range names mentioned as an example for the post (to protect Employee info). Thanks for the suggestion.

BTW, you're a rockstar. I ran the ISNUMBER function on the employee data sheet and discovered Quickbooks exports the date as plain text. I ran a column to the side with the DATEVALUE function (as I will have to continue exporting from QB) and it worked after that. Than you so much!

 

[MARK858]

You're welcome, you will probably find that you could select the offending column then click Data, click Text to Columns, click Next twice, check that General is selected and finally click Finish and it will put the column straight.

 

[rallysport096]

You're welcome, you will probably find that you could select the offending column then click Data, click Text to Columns, click Next twice, check that General is selected and finally click Finish and it will put the column straight.

That works as well. However, when I import further reports from QB, will I have to do that each time or can it be set to automatically fix it?

 

[mole999]

you can change the text value to a number by adding double minus

=A1 =--A1, then regardless of the download, the formula can correct

 

[MARK858]

@mole999, are you proposing changing the individual values in a separate column as the OP is already doing with Datevalue or amending the formula

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

in some way where the problem cells are in the Date named range?

If it is the latter can you post the proposed formula?

 

[rallysport096]

you can change the text value to a number by adding double minus

=A1 =--A1, then regardless of the download, the formula can correct

Amazed you all know this. Or that this was even developed in the first place .

The function I'm using now looks like this:

=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))

How could I apply the "=--A1" suggestion to that (where J:J would change to B:B, as QB enters the date in that column)? Also, is there any way to clean this up? I'd rather not use the sheet name for every argument, but rather refer to a cell on the same sheet with the same name as the worksheet I'm directing to. Example, E4, which has the employee's name (same as the name of the worksheet), could replace 'Employee Name'!.

*Edit - I was trying some =INDIRECT functions but with no luck. Was I on the right track?

 

[mole999]

@mole999, are you proposing changing the individual values in a separate column as the OP is already doing with Datevalue or amending the formula in some way where the problem cells are in the Date named range?

If it is the latter can you post the proposed formula?

Just the formula thats reading the text data