# Add into a formula Right & Len

#### Beh162

##### Board Regular
My current formula is =SUMPRODUCT(--(A11>='Front End'!B\$25:B\$72),--('Front End'!C\$25:C\$72>=A11+"00:15"))+SUMPRODUCT(--(A11>='Combo EE'!B\$36:B\$69),--('Combo EE'!C\$36:C\$69>=A11+"00:15"))

I'm having an issue because when I copy data in the range C25:C72 and B25:B72 the time now has a date in front of it. I've tried reformatting the cells, but the date won't disappear. So I was thinking of using the Right & Len function to move 8 or 9 places to the right so that it will see the time only. The formula above isn't counting the entries that now have the date.

Thanks for any help in this!

#### Delfinus25

##### Board Regular
You can subtract it from itself. If your date/time is in A2, use =A2-(Int(A2)). That should remove the date value.

#### Beh162

##### Board Regular
You can subtract it from itself. If your date/time is in A2, use =A2-(Int(A2)). That should remove the date value.
That would involve a helper cell right?

#### Delfinus25

##### Board Regular
That would involve a helper cell right?
Yes, it likely would. Do you mean you are pasting into these frequently and that isn't an option?

#### Beh162

##### Board Regular
Yes, it likely would. Do you mean you are pasting into these frequently and that isn't an option?
It’s an option but I’d have to create a whole helper section to copy the info in then put the formula you mentioned in the actual section. The formula happens 7 times Monday - Sunday and the data is entered once a week.

That’s why I assumed add to formula , but if this is best option I can create a helper section

#### jtakw

##### Well-known Member
Hi,

The reason the cell is showing "1/1/1900 1:00:00 PM" is because the cell value is 24 hours and higher (in this case, it's 37 hours, therefore the cell is basically showing 1 day 13 hours), RIGHT function won't work because the Real value in the cells is actually 1.541666667 formatted as DATE TIME.

If you don't care about the Day part of the value (anything 24 hours and above), you can add the MOD function in your formula like this:

MOD('Front End'!B\$25:B\$72,1)

MOD('Front End'!C\$25:C\$72,1)

Don't know the rest of your data, so don't know if this will work for you, just a suggestion if you want to ignore the day part of the values.

Edit: Just to show you what your modified formula will look like:

=SUMPRODUCT(--(A11>=MOD('Front End'!B\$25:B\$72,1)),--(MOD('Front End'!C\$25:C\$72,1)>=A11+"00:15"))+SUMPRODUCT(--(A11>='Combo EE'!B\$36:B\$69),--('Combo EE'!C\$36:C\$69>=A11+"00:15"))

Last edited:

#### Beh162

##### Board Regular
Yeah that showed as a Value error. I'm thinking... The 'Front End' doesn't have the dates in it because it's manually keyed once. It's only the Combo EE part of the formula that has to remove the date. Would that be a reason there was an error? I tried to adjust the formula to move the MOD to the COMBO part of it only, but got same error (not sure if that was error I did)
=SUMPRODUCT(--(A11>='Front End'!B\$25:B\$72),--('Front End'!C\$25:C\$72>=A11+"00:15"))+SUMPRODUCT(--(A11>=MOD('Combo EE'!B\$36:B\$69,1)),--(MOD('Combo EE'!C\$36:C\$69,1)>=A11+"00:15"))

A11 = 10:00AM
In Combo B36: B69 column has start time such as 10:00
C36:C69 column has end time such as 1:00

If it's easier I can screen shot a simplified view of what i'm achieving, or is the helper section the way to go

#### jtakw

##### Well-known Member
Showing some sample data might help.

Your OP said "I'm having an issue because when I copy data in the range C25:C72 and B25:B72 the time now has a date in front of it."

now you say "The 'Front End' doesn't have the dates in it because it's manually keyed once. It's only the Combo EE part of the formula that has to remove the date."

Last edited:

#### Beh162

##### Board Regular
Yes, that was completely my fault as I wasn't thinking about the first sheet being manual. I'll adjust OP and screen shot a simplified sheet

#### Beh162

##### Board Regular
Can't edit OP so the range I'm having trouble with is 'Combo EE' B36:B69 and C36:C69

The formula is counting how many people are there at the time referenced in A11 (10:00) the next formula down would reference A12 = 10:30 and so on in increments of 30 minutes
The part of the formula that looks at c36:c69 is counting how employees are still there minus 15 as they will be leaving and we don't want to count them as there.

This is where the formula is located in Cell B11

1,082,316
Messages
5,364,506
Members
400,804
Latest member
davileal

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...