Add into a formula Right & Len

Beh162

Board Regular
Joined
Jan 15, 2015
Messages
109
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
Joined
Feb 27, 2016
Messages
80
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
Joined
Jan 15, 2015
Messages
109
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
Joined
Jun 29, 2014
Messages
5,146
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
Joined
Jan 15, 2015
Messages
109
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
Joined
Jun 29, 2014
Messages
5,146
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
Joined
Jan 15, 2015
Messages
109
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
Joined
Jan 15, 2015
Messages
109
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top