Calculate Information based on time

Nancy Fite

New Member
Joined
Sep 30, 2006
Messages
5
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Sep 30, 2006
Messages
5
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

New Member
Joined
Sep 30, 2006
Messages
5

ADVERTISEMENT

I just got my HTML to work. Please see spreadsheet below:
Pame's Test.xls
CDEFGHIJKLMN
1CostQtyBreakfast$Lunch$Dinner$AfterHours$TimeEntered
2$0.44301.320010:156:0010:000
3$0.23200.460010:1510:0114:001
4$1.15101.150020:1014:0120:000
520:012:300
Sheet1
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

Board Regular
Joined
Jun 1, 2003
Messages
130
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

New Member
Joined
Sep 30, 2006
Messages
5
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

Board Regular
Joined
Jun 1, 2003
Messages
130
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.
 

Forum statistics

Threads
1,136,426
Messages
5,675,773
Members
419,585
Latest member
popsin

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
Top