Calculate Information based on time

[Nancy Fite]

Am having trouble writing correct statement and need some clear thinkers.

Need to calculate qty * cost and enter into a certain cell based on the time the information was entered. If the time entered in I2 was between K2 and L2 then the qty*cost would be entered into E2. Likewise, if I2 was between K3 and L3, then the qty*cost would be entered into F2 and so on for 4 more columns. I am using the 24 hour clock.

Any thoughts? I've worked on this for 3 days but haven't succeeded.

 

[larrydunn]

Try pasting these formulas into these cells

It worked for me, though it may be that I'm using some cells you need for other things!

I'm using column M for the factor 0 or 1, which is used to decide where the result goes.

If you're forced to use column N, for example, simply change all the M's to N's.

If you need more than 10 hour ranges, just continue the progression of formulas.

E2
=IF($M$2 = 0,0,ROUND($C$2*$D$2,2))
F2
=IF($M$3 = 0,0,ROUND($C$2*$D$2,2))
G2
=IF($M$4 = 0,0,ROUND($C$2*$D$2,2))
H2
=IF($M$5 = 0,0,ROUND($C$2*$D$2,2))
M2
=IF($I$2 >= $K18,IF($I$2 <= $L$2,1,0),0)
M3
=IF($I$2 >= $K$3,IF($I$2 <= $L$3,1,0),0)
M4
=IF($I$2 >= $K$4,IF($I$2 <= $L$4,1,0),0)
M5
=IF($I$2 >= $K$5,IF($I$2 <= $L$5,1,0),0)
M6
=IF($I$2 >= $K$6,IF($I$2 <= $L$6,1,0),0)
M7
=IF($I$2 >= $K$7,IF($I$2 <= $L$7,1,0),0)
M8
=IF($I$2 >= $K$8,IF($I$2 <= $L$8,1,0),0)
M9
=IF($I$2 >= $K$9,IF($I$2 <= $L$9,1,0),0)
M10
=IF($I$2 >= $K$10,IF($I$2 <= $L$10,1,0),0)

 

[barry houdini]

Not quite sure of your setup - you indicate you're copying the formula across which doesn't quite look right to me but the basic formula should be

=IF(AND(I2>=K2,I2<=L2),qty*cost,"")

then repeat for your other cells

 

[Nancy Fite]

Thank you for your responses. Barry, I wasn't able to get your formula to work, even when I changed the qty and cost to cell numbers.

Larry, yours came closer to working, but the 0's and 1's aren't working properly (I'm sure it's operator error). Here is more detail of the spreadsheet (I would have added the HTML but kept getting an error on it too - must be cursed!)

Columns
C = Qty D=Cost E=Breakfast F=Lunch G=Dinner H=After Hours I=Time Entered
In columns K&L I have the time slots K2-L2, K3-L3, K4-L4 & K5-L5

I changed the code in M2 to read $K$2 instead of $K18. The rest of the M cells are showing only 0's, even with values in the Qty, Cost & Time Entered cells.

Can you think of what I'm doing wrong. We're so close... just barely out of reach.

 

[Nancy Fite]

I just got my HTML to work. Please see spreadsheet below:

Pame's Test.xls
	C	D	E	F	G	H	I	J	K	L	M	N
1	Cost	Qty	Breakfast$	Lunch$	Dinner$	AfterHours$	TimeEntered
2	$0.44	3	0	1.32	0	0	10:15		6:00	10:00	0
3	$0.23	2	0	0.46	0	0	10:15		10:01	14:00	1
4	$1.15	1	0	1.15	0	0	20:10		14:01	20:00	0
5									20:01	2:30	0
Sheet1

 

[larrydunn]

Hi again Nancy. Glad we're getting closer. Sometimes you just have to sneak up on the answer.

I see one thing that's wrong. If you look in cell E2, you'll see a reference to M2. Look in cell E3, and you should see a reference to M3 - the same row. Instead, E3 refers to M2 again.

I think I didn't visualize your spreadsheet well enough. Adjust some of those references and parts of the result will straighten out. However, I believe you will have to MOVE some of the comparison formulas and stuff to accommodate your data. There's more complexity here than I knew.

Also, when I looked at F2 (I think!), it referred to M4. Each of these must refer to a 'factor' cell on its own row.

Getting a headache. Good Luck!

 

[barry houdini]

Do you want a total in M2 of all spending between the times shown in K2 and L2?

If so try this formula in M2 copied down

=SUMPRODUCT(--((I$2:I$4<=L2)+(I$2:I$4>=K2)=(L2>K2)+1),F$2:F$4)

 

[larrydunn]

Second Solution:

E2
=IF(M2 = 0,"",ROUND($C$2*$D$2,2))
F2
=IF(N2 = 0,"",ROUND($C$2*$D$2,2))
G2
=IF(O2 = 0,"",ROUND($C$2*$D$2,2))
H2
=IF(P2 = 0,"",ROUND($C$2*$D$2,2))

M2
=IF($I2 >= $K$2,IF($I2 <= $L$2,1,0),0)
N2
=IF($I2 >= $K$3,IF($I2 <= $L$3,1,0),0)
O2
=IF($I2 >= $K$4,IF($I2 <= $L$4,1,0),0)
P2
=IF((M2+N2+O2)> 0,0,1)

After placing these formulas in place, just copy them down as needed.

This method relies on four factors placed in columns m thru p. They must be copied down as well. They'll need to be in place for each row you have data in.

 

[Nancy Fite]

Thank you SOOOO much. I feel like a new person. My spreadsheet calculates properly and another problem resolved. You're the best and thanks for hanging in there with me!!!

 

[larrydunn]

I'm glad you're glad. Now for just one more refinement.

If you're like me, you'd be very happy not to have to type in the colon with each time entered.

If not, read no further.

To enter data without typing colon: for example, 1015 for 10:15

M2
IF(INT($I2) >= INT($K$2),IF(INT($I2) <= INT($L$2),1,0),0)
N2
IF(INT($I2) >= INT($K$3),IF(INT($I2) <= INT($L$3),1,0),0)
O2
IF(INT($I2) >= INT($K$4),IF(INT($I2) <= INT($L$4),1,0),0)

Now, change number format for times to Text. They will display as you type them. For example, 0815.
If you use General number format, the math works the same way, but 0815 becomes 815.

Either way, you must change the number format in the begin and end table entries to match.