Add into a formula Right & Len

[Beh162]

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]

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

 

[Beh162]

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]

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]

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]

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

 

[Beh162]

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]

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."

 

[Beh162]

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]

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