Add into a formula Right & Len

Beh162

Board Regular
Joined
Jan 15, 2015
Messages
130
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.

14tyce0.png

Thanks for any help in this!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
You can subtract it from itself. If your date/time is in A2, use =A2-(Int(A2)). That should remove the date value.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.

14vn3pt.png

This is where the formula is located in Cell B11
i3iecm.png
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top