Inexperienced user trying to get a formula to change depending on the day of the week

[mattn83]

Hello!

Like the title states, I am an inexperienced user trying to create a sheet that will calculate bar tips owed to staff. I have managed to piece together something that works, but have one problem I have not been able to find the answer to. I have one cell that I need the formula to change depending on if it's a Friday or Saturday as the hours open are different, and that changes that tipout for that individual. I have tried playing around with IF Weekday functions but can't get it to work, which is probably an error between the chair and the keyboard. I have found a few references to creating a table and pulling from that, but I simply don't really know how to do that. The formula in question is:

=SUM(D2/9)*(B13)*0.2

That is for Friday, if Saturday it needs to be "D2/12", very small change.

I've attached an image showing how I've got it set up. I create a copy for each day, and manually add the date in A2.

Any help would be beyond appreciated!!

 

[mattn83]

When I say manually add the date I type in "1/15/2023" and have it set up to show "Sunday, January 15, 2023"

 

[James006]

Hi,

You can test following
=IF(WEEKDAY(A2)=6,SUM(D2/9)*(B13)*0.2,IF(WEEKDAY(A2)=7,D2/12))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=(D2/IF(WEEKDAY(A2)=7,12,9))*B13*0.02

 

[mattn83]

Hi,

You can test following
=IF(WEEKDAY(A2)=6,SUM(D2/9)*(B13)*0.2,IF(WEEKDAY(A2)=7,D2/12))

I just tried this and get a return of FALSE, this is similar to what I've been trying. I'm playing around to see if I can get it to work

 

[James006]

I just tried this and get a return of FALSE, this is similar to what I've been trying. I'm playing around to see if I can get it to work

Getting FALSE for all the other days ....
What is your formula for the days which are not Fridays or Saturdays ...

 

[Fluff]

Did you try the formula I suggested?

 

[mattn83]

Hi & welcome to MrExcel.
How about

=(D2/IF(WEEKDAY(A2)=7,12,9))*B13*0.02

Hi & welcome to MrExcel.
How about

=(D2/IF(WEEKDAY(A2)=7,12,9))*B13*0.02

I tried this one, and it is returning a value of $4.76 for the above sheet, whereas the correct sum would be $47.60.

 

[mattn83]

Getting FALSE for all the other days ....
What is your formula for the days which are not Fridays or Saturdays ...

The only 2 days that cell is used is Friday and Saturday. That position does not work Sun-Thurs

 

[Fluff]

In your image B13 is empty, so it would return 0.