# Calculate Information based on time

#### Nancy Fite

##### New Member
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
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
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
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

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

#### barry houdini

##### MrExcel MVP

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

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

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.

Replies
0
Views
126
Replies
4
Views
47
Replies
11
Views
356
Replies
7
Views
122
Replies
4
Views
53

### Forum statistics

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.

### Which adblocker are you using?    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

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