"IF" formula problem

excelbeginner2

New Member
Joined
Jun 10, 2015
Messages
5
Hi I've looked on a couple of threads already and tried to adapt my formula using them but I'm a bit of a novice and need some help, please!

I am making a basic table which calculates wages depending upon hours worked, at the moment the user inputs a start time and end, a job role/wage and this calculates a total £ sum. However, in addition for this I wish for the total to be multiplied by 1.5 (*1.5) if the hours worked were on a Saturday to signify time and a half paid when employees work on this day, this is in another column.

I hope that's pretty clear for my first post, thanks in advance for your help! :)
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

mick0005

Active Member
Joined
Feb 21, 2011
Messages
406
Here you go. I don't know the columns/rows of your setup so I had to create a table on my own from scratch. In the future I'd recommend that you use the HTML Maker (see my signature) to do this for the forum members. It will save everyone time and will make people more likely to attempt to solve your problem.

My solution relies on the assumption that you have the date somewhere for each record, column A for my example.

Excel 2010
ABCDEFG
1DateIn timeOut timeWageHours WorkedPay
26/11/20159:00 AM5:00 PM158120
36/12/20159:00 AM5:00 PM158120
46/13/20159:00 AM5:00 PM158180
56/14/20159:00 AM5:00 PM158120
66/15/20159:00 AM5:00 PM158120
76/16/20159:00 AM5:00 PM158120
86/17/20159:00 AM5:00 PM158120
96/18/20159:00 AM5:00 PM158120
106/19/20159:00 AM5:00 PM158120
116/20/20159:00 AM5:00 PM158180
126/21/20159:00 AM5:00 PM158120

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
F2=(MOD(C2-B2,1))*24
G2=IF(TEXT(A2,"ddd")="Sat",(F2*D2)*1.5,F2*D2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,388
Messages
5,595,889
Members
414,029
Latest member
mrwilker

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
Top