Question

Sally Artisan Bakery makes a variety of organic bread. The bakery is open every day from 7 AM to 6 PM. At 4 PM each day, the bakery marks down its goods by 25%. It also offers a 10% discount every Sunday before 10 AM. Design a spreadsheet that displays the current date/time in one cell. Have your spreadsheet display the correct amount due when the user enters a sale amount, assuming no taxes will be charged.

Answer 1

Final output would be like this

1. 1. First open excel and in A1 Type "Time" in A2 Type " Day" Type the Text as shown in Column C
2. In E5 to E10 Type Your menu
3. Next Column is for Price Set That
4. Type Quantity
5. IN H13,H14,H15 Type the text as shown
6. Now after all the hardcoded text lets start the actual code and formula that wil help you to achieve this
7. Open Macro Editer by Hitting the Key Alt+F11
8. 
9. select this and then insert the following snippet

10. Dim SchedRecalc As Date
    
    Sub Recalc()
    
    With Sheet1.Range("B1")
    
    .Value = Format(Time, "hh:mm:ss AM/PM")
    
    End With
    
    Call SetTime
    
    End Sub
    
    Sub SetTime()
    
    SchedRecalc = Now + TimeValue("00:00:01")
    
    Application.OnTime SchedRecalc, "Recalc"
    
    End Sub
    
    Sub Disable()
    
    On Error Resume Next
    
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
    
    End Sub
    

11. After entering this paste the same script in SHEET1 in the left hand side and then save the file as .xlsm
12. now open the sheet again and hit alt+f8 slect SetTime from the list and give it a shortcut key, now everytime you open the exccel you need to hit the jey combination which you gave in step 12
13. after that you will see a clock ticking in B1, after that in B2 type the formula =TEXT(DAY(TODAY()),"dddd")
14. IN E1 Type formula : =IF(B1="","",IF(AND(B1>=(--"4:00 PM"),B1<(--"4:01 PM")),0.75,1))
15. In E2 Type Formula: =IF(B2="Sunday","yes","No")
16. In E3 Type Formula : =IF(B1="","",IF(B1<(--"10:00 AM"),0.9,1))
17. In L6 Typer Formula : =IF(ISBLANK(J6),0,J6*H6) and then drag till L10 it will the slected cell with 0
18. In L13 Type Formula : =SUM(L6:L10)
19. In L15 Type Formula : =IF(E2<>"yes",E1*SUM(L6:L10),E3*SUM(L6:L10))
20. in L14 Type Formula: =L13-L15
21. Now after that save the excel sheet and start again , and press the shortcut key selected in step 12
22. Fill the Values in Quantity section and all the fields will auto populate with the values

Feel free to reply if you face any issues