Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-28-2011, 08:57 PM
WinIDS WinIDS is offline Calculating Weekend Allowance Windows 7 64bit Calculating Weekend Allowance Office 2003
Novice
Calculating Weekend Allowance
 
Join Date: Aug 2011
Posts: 3
WinIDS is on a distinguished road
Default Calculating Weekend Allowance


Hi all,

I'm stuck with a calculation on weekend allowance. Workers would be paid a set allowance if they have worked on Sundays.

I have a spreadsheet set up to calculate the payment for an entire month. The dates lie on D2:AH2 and the number of hours worked per person are directly below, (e.g. D6:AH6,D7:AH7, etc). The number of hours worked would show 'A' if the person if absent.

To calculate the number of Sundays worked, I have tried SUMPRODUCT(--(D6:AH6>0),--(WEEKDAY($D$2:$AH$2)=1)), but it seems to give me 4 every time. Any ideas?
Reply With Quote
  #2  
Old 08-28-2011, 10:51 PM
nutrastat nutrastat is offline Calculating Weekend Allowance Windows 7 64bit Calculating Weekend Allowance Office 2010 32bit
Advanced Beginner
 
Join Date: Aug 2011
Location: Brighton, UK
Posts: 36
nutrastat is on a distinguished road
Default

Can you upload an example file, with just four personnel?

If you can, I will see what I can do!
Reply With Quote
  #3  
Old 08-28-2011, 11:15 PM
WinIDS WinIDS is offline Calculating Weekend Allowance Windows 7 64bit Calculating Weekend Allowance Office 2003
Novice
Calculating Weekend Allowance
 
Join Date: Aug 2011
Posts: 3
WinIDS is on a distinguished road
Default

Sure thing.
Attached Files
File Type: xls Employee Snippet.xls (30.0 KB, 18 views)
Reply With Quote
  #4  
Old 08-29-2011, 12:45 AM
nutrastat nutrastat is offline Calculating Weekend Allowance Windows 7 64bit Calculating Weekend Allowance Office 2010 32bit
Advanced Beginner
 
Join Date: Aug 2011
Location: Brighton, UK
Posts: 36
nutrastat is on a distinguished road
Default Separate Requirement Into Separate Weeks?

Good Morning,

Well, it took some thinking about because I do not know how to deal with arrays. Attached you will find a file, that although not an elegant solution, is a least a solution to your problem.

What I have done is created a range for Week_01, then using a horizontal look up, looked down to see whether the Sunday cell was an 'A' or zero and giving a result of 0, or 1. You will need to do the same for the remaining weeks.

Hope this helps.
Attached Files
File Type: xls Nutrastat Employee Snippet (1).xls (42.5 KB, 6 views)
Reply With Quote
  #5  
Old 08-29-2011, 01:42 AM
Catalin.B Catalin.B is offline Calculating Weekend Allowance Windows Vista Calculating Weekend Allowance Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Usually. it is a bad idea to mix in a table numbers and text
Maybe you can get rid of the "A" in this table, and your formula will work, with a little modification
your formula evaluates the range , and counts how many cells are >0 (and no mattes what values you have in D9:AH9...
=SUMPRODUCT(--(D9:AH9>0);--(WEEKDAY($D$3:$AH$3)=1))
that is why, you get 4 as the result, for excel the "A" cells are >0 , and the result means that there are 4 cells in the sum range for sunday...
try instead of "A" leave the cells blank (and make your conditional formatting formula to mark the blanks with red), and remove >0 from formula, you will get the number of hours worked in sundays...
try this:
=SUMPRODUCT(--(D7:AH7);--(WEEKDAY($D$3:$AH$3)=1))
Reply With Quote
  #6  
Old 09-01-2011, 08:11 PM
WinIDS WinIDS is offline Calculating Weekend Allowance Windows 7 64bit Calculating Weekend Allowance Office 2003
Novice
Calculating Weekend Allowance
 
Join Date: Aug 2011
Posts: 3
WinIDS is on a distinguished road
Default

@nutrastat: That works!

And yeah - The letters are actually causing the troubles with the formula. I'll see if I can change it without breaking something.

Thanks all!
Reply With Quote
  #7  
Old 09-02-2011, 01:27 AM
Catalin.B Catalin.B is offline Calculating Weekend Allowance Windows Vista Calculating Weekend Allowance Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

well, i guess you can mark your thread as solved...
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating Weekend Allowance Calculating dates Daria11 Word VBA 1 06-08-2011 06:54 PM
calculating points and division in excel edward masoya Excel 1 05-09-2011 06:31 AM
Calculating Weekend Allowance Calculating age at date jamierbooth Excel 2 02-22-2011 03:35 AM
Calculating Form Fields in Microsoft Word wubba80 Word 1 06-25-2010 12:42 AM
Code to auto-forward weekend messages Whang Outlook 0 06-16-2010 02:18 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft