Sum hours if cell not blank and change value dependant on day of the week

[IsabellaLearning]

Hi all

This one is not for work but for me at home - I am trying to calculate hours the carers come and look after mum as I always feel like the charging is not correct


What I am trying to achieve


if Monday to friday(column A) SUM hours from column B-D as 1 each in column E (Now done manually)


if Saturday (Column A) sum hours from column B-D as 1.4 each in column E (Now done manually)


if Sunday (Column A) sum hours from column B-D as 1.7 each in column E (Now done manually)


If column F says yes to public holiday SUM hours from column B-D as 1.8


In column J enter the total hours for each month from column E


Date in column A must remain in current format to show day of the week


Confusing I know so I have here the manual sample sspreadsheet https://sabercathost.com/51du/Ma_Roster.xlsx....

thank you sooooo much in advance, I love this forum and you people who take the time out to assist over the years!

Izzy

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	Mon Sep 25, 2017	1	2	3	6
2	Tue Sep 26, 2017	1	2	3	6
3	Wed Sep 27, 2017	1	2	3	6
4	Thu Sep 28, 2017	1	2	3	6
5	Fri Sep 29, 2017	1	2	3	6
6	Sat Sep 30, 2017	1	2	3	8.4
7	Sun Oct 01, 2017	1	2	3	10.2
8	Mon Oct 02, 2017	1	2	3	10.8	Yes
9
3a

Cell Formulas
Range	Formula
E1	=IF(F1="yes",SUM(B1:D1)*1.8,SUM(B1:D1)*LOOKUP(WEEKDAY(A1,2),{1,1;6,1.4;7,1.7}))

N.B. I did not view your file.
Copy or fill the formula down.

 

[IsabellaLearning]

Dave thankyou so much for responding!!!

The formula does work, i believe its because i didnt correctly explain that what you will be counting in column B, C and D will appear as a time not a number - so i have edited your table with the correct answers. EG: when any row has a time in it its always worth:
1 hr - Mon - Fri
1.4 on Saturdays
1.7 on Sundays
1.8 on Mondays

Is that possible?

Iz

	A	B	C	D	E	F
Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Mon Sep 25, 2017[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Tue Sep 26, 2017[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Wed Sep 27, 2017[/TD]
[TD="align: right"]

7:00

<tbody>
</tbody>

[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]5.4[/TD]

[TD="align: center"]9[/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>

3a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=IF(F1="yes",SUM(B1:D1)*1.8,SUM(B1:D1)*LOOKUP(WEEKDAY(A1,2),{1,1;6,1.4;7,1.7}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



N.B. I did not view your file.
Copy or fill the formula down.[/QUOTE]

****** id="cke_pastebin" style="position: absolute; top: 180px; width: 1px; height: 1px; overflow: hidden; left: -1000px;"><table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);="" border-collapse:="" collapse;"="" width="" style="font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; text-align: right;"><tbody>[TR]
[TD]11:00[/TD]
[TD]14:00[/TD]
[/TR]
</tbody></table></body>

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	Mon Sep 25, 2017	7:00			1
2	Tue Sep 26, 2017	7:00	11:00	14:00	3
3	Wed Sep 27, 2017	8:00	15:00		2
4	Thu Sep 28, 2017	8:00	15:00		2
5	Fri Sep 29, 2017	8:00	15:00		2
6	Sat Sep 30, 2017	8:00	15:00		2.8
7	Sun Oct 01, 2017	8:00	15:00		3.4
8	Mon Oct 02, 2017	8:00	15:00		3.6	Yes
9
3a

Cell Formulas
Range	Formula
E1	=IF(F1="yes",COUNT(B1:D1)*1.8,COUNT(B1:D1)*LOOKUP(WEEKDAY(A1,2),{1,1;6,1.4;7,1.7}))

 

[IsabellaLearning]

Dave thanks for sticking with me - nearly there some are working!!

Anything that shows up as 10:00 or 11:00 for some reason is summing up as 2? eg red below

Excel 2010

	A	B	C	D	E	F
Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Mon Sep 25, 2017[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"]wrong[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Tue Sep 26, 2017[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Wed Sep 27, 2017[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 3 [/TD]
[TD="align: right"]wrong[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Thu Sep 28, 2017[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Fri Sep 29, 2017[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Sat Sep 30, 2017[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Sun Oct 01, 2017[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]Mon Oct 02, 2017[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.6[/TD]

[TD="align: center"]9[/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>

3a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=IF(F1="yes",COUNT(B1:D1)*1.8,COUNT(B1:D1)*LOOKUP(WEEKDAY(A1,2),{1,1;6,1.4;7,1.7}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[IsabellaLearning]

Dave disregard my last comment the numbers are all correct - it was hte weekends and i had not set numbers up as with decimals - awesome and thankyou so much !!! i think its amazing when people come up with these formulas - such a gift!