IF 11:00 PM, do nothing. otherwise do this...

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Newb here so bear with me if the answer is obvious!

Situation
: Working on a real life business situation where we are looking at current store operating hours and deciding if we need to close early if we are doing less than $40 in sales. I can write the formula for that - no problem!
Hypothetical Example: =IF(F2<40,MOD(E2-TIME(1,0,0),1),E2). This says, I store is doing less than $40 in sales, look at the current time it closes and make it close one hour earlier (i.e. 10:00pm)
Problem: I want to add one more condition to this formula. If store is currently closing at 11:00, then I do not want it to close any earlier. Stated another way, if store is doing less than $40 in sales from 10:00pm to 10:59pm then I do not want it to close at 10:00pm, I want to keep it as is - open till 11:00pm
Current formula where I get stuck: =IF(currently closing at 11:00pm, then leave closing at 11:00pm, otherwise do this -----> IF(F2<40,MOD(E2-TIME(1,0,0),1),E2)
System: Windows 10 Pro

Appreciate any help you can provide on this formula!


CDEFTUWZAAABACADAE
1STOREPUW CLOSE SUN-THUP10 '17-P3 '18 LAST HOUR SALESWINTER HOURS RECCO
SUN-THU
6Stuart11:00:00 PM $ 64.0411:00:00 PM<-because this store does more than $40, it stays open at 11:00pm
8Nashville11:00:00 PM $ 78.8511:00:00 PM<-because this store does more than $40, it stays open at 11:00pm
9Berkeley11:00:00 PM $ 115.5711:00:00 PM<-because this store does more than $40, it stays open at 11:00pm
10Lillington11:00:00 PM $ 38.6010:00:00 PM<-because this store does less than $40, the current formula makes it close at 10:00. However, 11:00pm is the earliest I want it to close, so how do I leave it closing at 11:00pm?
11Morehead City12:00:00 AM $ 17.1311:00:00 PM<-because this store less than $40, it will close one hour earlier at 11:00pm
12Ahoskie11:00:00 PM $ 104.2711:00:00 PM<-because this store does more than $40, it stays open at 11:00pm
13Williamston11:00:00 PM $ 82.0311:00:00 PM<-because this store does more than $40, it stays open at 11:00pm

<tbody>
</tbody>
Greensboro

Worksheet Formulas
CellFormula
E6=VLOOKUP(B6,'S:\Wendy''s Marketing\Store Hours Audit\Latest\Latest and Greatest\[NPC Wendy''s Store Hours LATEST 8-27-18.xlsx]wendys'!$C$7:$BQ$431,66,FALSE)
F6=VLOOKUP(B6,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)
U6=IF(F6<40,MOD(E6-TIME(1,0,0),1),E6)
E8=VLOOKUP(B8,'S:\Wendy''s Marketing\Store Hours Audit\Latest\Latest and Greatest\[NPC Wendy''s Store Hours LATEST 8-27-18.xlsx]wendys'!$C$7:$BQ$431,66,FALSE)
F8=VLOOKUP(B8,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)
E9=VLOOKUP(B9,'S:\Wendy''s Marketing\Store Hours Audit\Latest\Latest and Greatest\[NPC Wendy''s Store Hours LATEST 8-27-18.xlsx]wendys'!$C$7:$BQ$431,66,FALSE)
F9=VLOOKUP(B9,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)
E10=VLOOKUP(B10,'S:\Wendy''s Marketing\Store Hours Audit\Latest\Latest and Greatest\[NPC Wendy''s Store Hours LATEST 8-27-18.xlsx]wendys'!$C$7:$BQ$431,66,FALSE)
F10=VLOOKUP(B10,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)
E11=VLOOKUP(B11,'S:\Wendy''s Marketing\Store Hours Audit\Latest\Latest and Greatest\[NPC Wendy''s Store Hours LATEST 8-27-18.xlsx]wendys'!$C$7:$BQ$431,66,FALSE)
F11=VLOOKUP(B11,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)
E12=VLOOKUP(B12,'S:\Wendy''s Marketing\Store Hours Audit\Latest\Latest and Greatest\[NPC Wendy''s Store Hours LATEST 8-27-18.xlsx]wendys'!$C$7:$BQ$431,66,FALSE)
F12=VLOOKUP(B12,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)
E13=VLOOKUP(B13,'S:\Wendy''s Marketing\Store Hours Audit\Latest\Latest and Greatest\[NPC Wendy''s Store Hours LATEST 8-27-18.xlsx]wendys'!$C$7:$BQ$431,66,FALSE)
F13=VLOOKUP(B13,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)
U8=IF(F8<40,MOD(E8-TIME(1,0,0),1),E8)
U9=IF(F9<40,MOD(E9-TIME(1,0,0),1),E9)
U10=IF(F10<40,MOD(E10-TIME(1,0,0),1),E10)
U11=IF(F11<40,MOD(E11-TIME(1,0,0),1),E11)
U12=IF(F12<40,MOD(E12-TIME(1,0,0),1),E12)
U13=IF(F13<40,MOD(E13-TIME(1,0,0),1),E13)

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I don't understand how you know that you want the store to stay open until 11 even if it has low sales.

In your example, what is the difference between Lillington being under $40 but staying open to 11, vs. another store that would be under $40 but close at 10? What is the criterion you could use in a formula?
 
Upvote 0
I don't understand how you know that you want the store to stay open until 11 even if it has low sales.

In your example, what is the difference between Lillington being under $40 but staying open to 11, vs. another store that would be under $40 but close at 10? What is the criterion you could use in a formula?

Good question. The criterion is based upon closing hour rules / standards that are set by the company. For example, no store shall close earlier than 11 regardless of how low sales are. Even if sales were $1, they would still remain open until 11. Stated another way, no store can close at 10 pm, no matter what.

Does that help?
 
Upvote 0
OK, hang on. So if last-hour sales are <$40, you want the store to close 1 hour early, but you never want any store to close earlier than 11 PM. Is that it? OK, I misunderstood, I thought that only applied to some stores.

I think you want something like this:

=IF(F8<40,MAX(TIME(23,0,0),MOD(E8-TIME(1,0,0),1)),E8)
 
Upvote 0
OK, hang on. So if last-hour sales are <$40, you want the store to close 1 hour early, but you never want any store to close earlier than 11 PM. Is that it? OK, I misunderstood, I thought that only applied to some stores.

I think you want something like this:

=IF(F8<40,MAX(TIME(23,0,0),MOD(E8-TIME(1,0,0),1)),E8)

Exactly! BRILLIANT! Thanks so much. (y)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
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