Formula Help

B

bernie1973

New Member
Joined
Feb 25, 2017
Messages
6
I am new to excel and self teaching, most likely doing it all the long way but every day is a learning day

I am attempting to extract information from worksheet 1 into worksheet 2.

Worksheet 1 is a record of employees and their hours worked on a daily basis for the full year.

1 employee in per row for 50 rows, 365 columns for each day of the year.

I am required to report back on a weekly basis the hours each individual has taken for annual leave, worked, overtime, sick leave etc

Below is an extract from worksheet 1 looks like

01020304050607080910111213141516
SunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMon
Employee 1 ddnn ddnnn
Employee 2 ddnn ddnnn
Employee 3 ddnn ddnnn
Employee 4 ddnn ddnnn
Employee 5 ddnn ddnnn
Employee 6 ddnn ddnnn
Employee 7 ddnn ddnnn
Employee 8 ddnn ddnnn
<colgroup><col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;" span="16"> <tbody> </tbody>

Worksheet 2 layout:

Week NoWeek StartWeek EndingAnnual Leave (A)Over time (OT)
101 Jan07 Jan
208 Jan14 Jan
315 Jan21 Jan
422 Jan28 Jan
529 Jan04 Feb
605 Feb11 Feb
712 Feb18 Feb
819 Feb25 Feb
926 Feb04 Mar
<colgroup><col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="4"> <tbody> </tbody>


The formula I am looking for is to be inserted into worksheet 2, from the information be ascertained from worksheet 1 so for calculating for total annual leave at week 1 for all employees the range would be B8:H51, with the permutations to count being a = 12 hours, a11 = 11 hours, a10 = 10hours leave taken all the way down to a1 where only 1 hour of leave has been taken.

Hopefully I have explained it good enough, if not please get back in touch.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Hello,

For a start ... in Sheet2, you could test in cell D2

=SUMPRODUCT(--(INDIRECT("Sheet1!"&ADDRESS(8,DAY($B2)+1,4)&":"&ADDRESS(51,DAY($C2)+1,4))="d"))

This formula will work the number of "d" ...(there are no "a" in your sample...) in sheet 1 in your range B8:H51 ... for week 1 ... to be copied down ...

Hope this will help
 
Upvote 0
Hi James,006

Apologies in missing out the "a" references as I lost focus, below is a section from February with a's added in, changing the "d" to an "a" as per your formula works ideal, however the added problem I have is that sometimes employees don't always take a full shift off and just part of the shift, they must take annual leave to the full hour, therefore I have 12 options so for annual leave a a11, a10,a9,a8,a7,a6,a5,a4,a3,a2,a1. where the a prefix denotes annual and the number accounts for the hours taken "a" means a 12 hours shift taken off on annual leave

To complicate the sums for the week, I would require to have a formula that can account/sum for the 12 x a permutations, hope you can resolve and many thanks again

0102030405060708091011121314151617181920
WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMon
nn ddnnn OTddn
nn dda2nn TRRn
nn ddnnn dddn
nn da6nnn dddn
nn a8dnnn dCOWCOWn
nn ddnnn dddn
nn ddnnn dddn
nn rrAnnn dddn
nn drrnnn AAAn
nn ddnnn dddn
<colgroup><col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" span="20"> <tbody>
</tbody>
 
Upvote 0
Hi,

Still for Sheet 2 ... in cell D2 ... a second attempt :

=SUMPRODUCT(--(ISNUMBER(SEARCH("A",INDIRECT("Sheet1!"&ADDRESS(8,DAY($B2)+1,4)&":"&ADDRESS(51,DAY($C2)+1,4)),1))))

Hope this will help
 
Last edited:
Upvote 0
Hi James,<o:p></o:p>
Thanks again for the formula, we are almost there and I thankyou for your patience, the formula is not capturing all A’s within its addressrange ie its picking up 2 out of say 14 for a week range of cells, I can’t seewhere/how it is doing this <o:p></o:p>
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></v:path></v:stroke></v:shapetype><v:shape id="Picture_x0020_2" style="width: 366.75pt; height: 308.25pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1025"> <v:imagedata o:title="" src="file:///C:\Users\DunlopB\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata></v:shape><o:p></o:p>
In Sheet 2 I have:<o:p></o:p>
Week No<o:p></o:p>​
Week Start<o:p></o:p>​
Week Ending<o:p></o:p>​
Annual Leave (A)<o:p></o:p>​
Countif<o:p></o:p>​
1<o:p></o:p>​
01 Jan<o:p></o:p>​
07 Jan<o:p></o:p>​
2<o:p></o:p>​
<o:p></o:p>​
2<o:p></o:p>​
08 Jan<o:p></o:p>​
14 Jan<o:p></o:p>​
0<o:p></o:p>​
<o:p></o:p>​
3<o:p></o:p>​
15 Jan<o:p></o:p>​
21 Jan<o:p></o:p>​
2<o:p></o:p>​
<o:p></o:p>​
4<o:p></o:p>​
22 Jan<o:p></o:p>​
28 Jan<o:p></o:p>​
0<o:p></o:p>​
<o:p></o:p>​
5<o:p></o:p>​
29 Jan<o:p></o:p>​
04 Feb<o:p></o:p>​
4<o:p></o:p>​
<o:p></o:p>​
6<o:p></o:p>​
05 Feb<o:p></o:p>​
11 Feb<o:p></o:p>​
2<o:p></o:p>​
134.75<o:p></o:p>​
7<o:p></o:p>​
12 Feb<o:p></o:p>​
18 Feb<o:p></o:p>​
2<o:p></o:p>​
208.25<o:p></o:p>​
8<o:p></o:p>​
19 Feb<o:p></o:p>​
25 Feb<o:p></o:p>​
0<o:p></o:p>​
159<o:p></o:p>​
9<o:p></o:p>​
26 Feb<o:p></o:p>​
04 Mar<o:p></o:p>​
4<o:p></o:p>​
0<o:p></o:p>​
10<o:p></o:p>​
05 Mar<o:p></o:p>​
11 Mar<o:p></o:p>​
2<o:p></o:p>​
0<o:p></o:p>​
11<o:p></o:p>​
12 Mar<o:p></o:p>​
18 Mar<o:p></o:p>​
2<o:p></o:p>​
0<o:p></o:p>​
<tbody> </tbody>
<o:p> </o:p>
The Annual Leave column is using your formula – please notethere is no data inputted yet for weeks 9,10 and 11 and its showing a return,for the countif column – this is where I have added my long countif formula – Ihave not populated it for weeks 1 to 5, this is returning in hours as previously communicated
Below is a very long “countif” formula summing what I amlooking for result wise but I have to manually change for each and every newrow/cell sun to sat week range<o:p></o:p>
=COUNTIF('Sheet1'!$BP$8:$BV$51,"a")*12.25+COUNTIF('Sheet1'!$BP$8:$BV$51,"a11")*11+COUNTIF('Sheet1'!$BP$8:$BV$51,"a10")*10+COUNTIF('Sheet1’!$BP$8:$BV$51,"a9")*9+COUNTIF('Sheet1'!$BP$8:$BV$51,"a8")*8+COUNTIF('Sheet1'!$BP$8:$BV$51,"a7")*7+COUNTIF('Sheet1'!$BP$8:$BV$51,"a6.25")*6.25+COUNTIF('Sheet1'!$BP$8:$BV$51,"a6")*6+COUNTIF('2017'!$BP$8:$BV$51,"a5")*5+COUNTIF('Sheet1'!$BP$8:$BV$51,"a4")*4+COUNTIF('Sheet1'!$BP$8:$BV$51,"a3")*3+COUNTIF('Sheet1'!$BP$8:$BV$51,"a2")*2+COUNTIF('Sheet1'!$BP$8:$BV$51,"a1")*1<o:p></o:p>
Again any help to simplify would be brilliant – or is therea way I can attach the excel workbook rather than screen shots?<o:p></o:p>
 
Upvote 0
01/01/201702/01/201703/01/201704/01/201705/01/201706/01/201707/01/201708/01/201709/01/201710/01/201711/01/201712/01/201713/01/201714/01/201715/01/201716/01/2017
SunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMon
Employee 1worked normal 898888 8888888
worked overtime 3 3 4
annual leave
sick leave
Employee 2worked normal 8 88 8888888
worked overtime 3 4
annual leave
sick leave 888
Employee 3worked normal 848888 8888888
worked overtime 3 24
annual leave 4
sick leave
Employee 4worked normal 88 888 8888888
worked overtime 3 32
annual leave
sick leave 8
01/01/201708/01/2017
07/01/201714/01/2017
worked normalworked overtimeannual leavesick leaveworked normalworked overtimeannual leavesick leave
Employee14930040700
Employee224302440400
Employee34434040600
Employee44030840500
as you have weeks/ employees and 4 time categories
the above is the first step
from that you can pull employee with most sick leave, overtime worked etc
the formula giving employee1 first week = 49 is
=SUMPRODUCT(($C$1:$R$1>=$G$22)*($C$1:$R$1<=$G$23)*($C3:$R3))
I copied this down and across then had to make manual adjustments to formulas
if this approach is acceptable we could try and make a single formula universal

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>
 
Upvote 0
I am required to report back on a weekly basis the hours EACH INDIVIDUAL has taken for annual leave, worked, overtime, sick leave etc

do you want for each week or for each employee - I am confused (easily)
 
Upvote 0
I require for a weekly total for all employees on annual leave, worked, overtime, sick leave, special leave etc, that is accessed by the director for his reports all on sheet 2

On a monthly / annual basis for each employee for their annual leave, worked, overtime, sick leave, special leave etc this is in-house for our own record keeping/tracking all on sheet 1

Sheet 1 is working fine, its the recording on Sheet 2 for each week (Sun to Sat) is the headache, firstly to distinguish from the 365 columns the dates for each week No and then extracting the 7 x 51 cells with different codes, a prefix for annual, d for dayshift, n for nightshift, OT for overtime, SCS for self certified sick, then NO, SPL, CS, UD and a couple more

These codes if just the letters are calculated at 12 hours, however, staff may have a split day where they have 7 hours annual and 5 hours TOIL where we record the 7 hours annual as a7 and the TOIL as TO5.

Hoping the above clarifies the muddy water I think I have created

Cheers
 
Upvote 0
bernie1973 said:
Hi James,<o:p></o:p>
Thanks again for the formula, we are almost there and I thankyou for your patience, the formula is not capturing all A’s within its addressrange ie its picking up 2 out of say 14 for a week range of cells, I can’t seewhere/how it is doing this <o:p></o:p>
Click to expand...

Hi again,

The search function is case sensitive ... and does not consider "A" as identical to "a" ...

Another attempt for cell D2 :

=SUMPRODUCT(--(LEFT(INDIRECT("Sheet1!"&ADDRESS(3,DAY($B2)+1,4)&":"&ADDRESS(51,DAY($C2)+1,4)),1)="a"))

Hope this will help
 
Upvote 0
01/01/201702/01/201703/01/201704/01/201705/01/201706/01/201707/01/201708/01/201709/01/201710/01/201711/01/201712/01/201713/01/201714/01/201715/01/201716/01/201717/01/201718/01/201719/01/201720/01/201721/01/201722/01/201723/01/201724/01/201725/01/201726/01/201727/01/201728/01/201729/01/201730/01/201731/01/201701/02/201702/02/2017
emp1WWWWW WWWWW WWWWW WWWWW WWWWW
emp2WWSSW WWWWW WWWWW WWWWW WWWWW
emp3WWWWW15 WWWWW WWWWW WWWWW WWWWW
emp4AAAAA WWWWW WWWWW WWWWW WWWWW
emp5WWWWT8 WWWWW WWWWW WWWWW WWWWW
emp6WWLLW WWWWW WWWWW WWWWW WWWWW
emp7WWWWW WWWWW WWWWW WWWWW WWWWW
emp8WWSSW WWWWW WWWWW WWWWW WWWWW
emp9WWWWW15 WWWWW WWWWW WWWWW WWWWW
emp10AAAAA WWWWW WWWWW WWWWW WWWWW
emp11WWWWT8 WWWWW WWWWW WWWWW WWWWW
emp12WWL6L6W WWWWW WWWWW WWWWW WWWWW
emp13AAAAA WWWWW WWWWW WWWWW WWWWW
emp14WWWWT8 WWWWW WWWWW WWWWW WWWWW
emp15WWLLW WWWWW WWWWW WWWWW WWWWW
emp16WWWW6W6 WWWWW WWWWW WWWWW WWWWW
W1412 HOURS WORKED_TOT HOURS
SSICK
AANNUAL LEAVE
T4TIME OFF IN LIEU_HOURS
LSPECIAL LEAVE
01/01/201708/01/201715/01/201722/01/201729/01/201705/02/201712/02/201719/02/2017this macro has looked at the data for first week (for test purposes)
07/01/201714/01/201721/01/201728/01/201704/02/201711/02/201718/02/201725/02/2017and produced the lower table (S=1 day) (annual leave in days)
WORKED612
OVERTIME6For j = 3 To 9
SICK4 For k = 3 To 18
ANNUAL LEAVE15 temp = Cells(k, j)
LIEU TIME66 suff = Mid(Cells(k, j), 2, 2)
SPEC LEAVE4 Lenn = Len(Cells(k, j))
If temp = "L" Then spleave = spleave + 1
If temp = "A" Then leave = leave + 1
If temp = "S" Then sick = sick + 1
If temp = "W" Then worked = worked + 12
If Lenn > 1 And Left(Cells(k, j), 1) = "W" Then GoTo 50 Else GoTo 51
50 If suff > 12 Then worked = worked + 12: ot = ot + suff - 12: GoTo 60
worked = worked + suff: GoTo 60
51 If Lenn > 1 And Left(Cells(k, j), 1) = "L" Then LOOO = LOOO + Mid(Cells(k, j), 2, 2)
60 Next k
Next j
Cells(32, 8) = worked
Cells(33, 8) = ot
Cells(34, 8) = sick
Cells(35, 8) = leave
Cells(37, 8) = spleave
Cells(36, 8) = LOOO
100 End Sub

<colgroup><col><col><col span="2"><col><col><col><col span="2"><col span="26"><col></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Personnel Roster using Macro's
Replies
0
Views
172
womeclr
W
C
  • Question
Pivot Table from Power Query - reorder Columns (Excel 2021)
Replies
4
Views
280
craigcurtin
C
J
  • Solved
help finding my error
Replies
1
Views
135
Proulxs
P
J
  • Solved
Formula Filled Dates
Replies
4
Views
329
Fluff
Fluff
J
  • Question
sumifs formula using date ranges as criteria
Replies
1
Views
101
Phuoc
P

Forum statistics

Threads
1,214,661
Messages
6,120,796
Members
448,994
Latest member
rohitsomani

Share this page

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
Back
Top