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

T

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
STOREPUW CLOSE SUN-THUP10 '17-P3 '18 LAST HOUR SALES
Stuart<-because this store does more than $40, it stays open at 11:00pm
Nashville<-because this store does more than $40, it stays open at 11:00pm
Berkeley<-because this store does more than $40, it stays open at 11:00pm
Morehead City<-because this store less than $40, it will close one hour earlier at 11:00pm
Ahoskie<-because this store does more than $40, it stays open at 11:00pm
Williamston<-because this store does more than $40, it stays open at 11:00pm

<tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="align: center"]WINTER HOURS RECCO
SUN-THU[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"] $ 64.04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: center"]11:00:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"] $ 78.85[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: center"]11:00:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"] $ 115.57[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: center"]11:00:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Lillington[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]11:00:00 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"] $ 38.60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]10:00:00 PM[/TD]
[TD="align: center"]<-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?[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"] $ 17.13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: center"]11:00:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"] $ 104.27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: center"]11:00:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"] $ 82.03[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E7E6E6]#E7E6E6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: center"]11:00:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Greensboro​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=VLOOKUP(B6,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U6[/TH]
[TD="align: left"]=IF(F6<40,MOD(E6-TIME(1,0,0),1),E6)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F8[/TH]
[TD="align: left"]=VLOOKUP(B8,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E9[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F9[/TH]
[TD="align: left"]=VLOOKUP(B9,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E10[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F10[/TH]
[TD="align: left"]=VLOOKUP(B10,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E11[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F11[/TH]
[TD="align: left"]=VLOOKUP(B11,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E12[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F12[/TH]
[TD="align: left"]=VLOOKUP(B12,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E13[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F13[/TH]
[TD="align: left"]=VLOOKUP(B13,'[QP SBH Master.xlsx]weekday'!$B$9:$BB$835,31,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U8[/TH]
[TD="align: left"]=IF(F8<40,MOD(E8-TIME(1,0,0),1),E8)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U9[/TH]
[TD="align: left"]=IF(F9<40,MOD(E9-TIME(1,0,0),1),E9)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U10[/TH]
[TD="align: left"]=IF(F10<40,MOD(E10-TIME(1,0,0),1),E10)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U11[/TH]
[TD="align: left"]=IF(F11<40,MOD(E11-TIME(1,0,0),1),E11)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U12[/TH]
[TD="align: left"]=IF(F12<40,MOD(E12-TIME(1,0,0),1),E12)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U13[/TH]
[TD="align: left"]=IF(F13<40,MOD(E13-TIME(1,0,0),1),E13)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
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
6StringJazzer said:
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?
Click to expand...

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
6StringJazzer said:
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)
Click to expand...

Exactly! BRILLIANT! Thanks so much. :beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Formula for if time is same, earlier or later?
2
Replies
12
Views
2K
Scott Huish
Scott Huish
G
  • Question Question
Overtime Calculation Based on a 40 hour week - Large Data Set
Replies
5
Views
1K
GR00007
G
T
  • Question Question
If time is 1:00 AM throws value if false
Replies
7
Views
901
tblackwell
T
M
  • Question Question
Selectively order data from specific customer on a new sheet, effectively creating a mini invoice of a few columns
2
Replies
10
Views
1K
milonius
M
P
  • Question Question
How do you setup a command button to execute a report that uses formulas, raw data on 1 worksheet, to input data in cells in client report worksheet?
Replies
0
Views
521
Pat1009
P

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

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