 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008Counting shifts lost between two dates
Hi Group, I'm working on a sickness monitoring sheet and I have four columns similar to below which provide a lookup to dates on duty. I've put together a formula (might be long winded) to calculate total days off between two dates but now need to refer to my lookup table and calculate how many days lost between those two dates. Its got me stumped so far, can anyone point me in the right direction please or pass comment on my totals formula. If I can get this to work I intended to have a couple of IF functions to check if its a whole month to count through or part as already in my totals. As always any help would be great. Regards Don Total formula =IF($B4="","", IF(AND(ISBLANK($C4),$B4<G$3,$B4>=F$3,$B$1>G$3),G$3-$B4+N("Counts from sick day to today if fit day is null"), IF(AND(ISBLANK($C4),$B4<G$3,$B$1>G$3),G$3-F$3+N("Counts from sick day to today if fit day is null"), IF(AND($B4<=F$3,$C4>F$3,$C4<=G$3),$C4-F$3+N("sick < = start Fit > start but < end of month"), IF(AND($B4>=F$3,$C4>F$3,$C4<G$3),$C4-$B4+N("sick >= start and fit < month end"), IF(AND($B4>=F$3,$B4<G$3,$C4>G$3),G$3-$B4+N("sick >= start but before end fit after end"), IF(AND($B4<F$3,$C4>G$3),G$3-F$3+N("sick before start fit after end"), 0))))))) Shift lookup table 01/01/2007 1 02/01/2007 1 03/01/2007 1 04/01/2007 1 05/01/2007 06/01/2007 07/01/2007 08/01/2007 09/01/2007 1 10/01/2007 1 11/01/2007 1 12/01/2007 1 13/01/2007 14/01/2007 15/01/2007 16/01/2007 17/01/2007 1 18/01/2007 1 19/01/2007 1 20/01/2007 1 21/01/2007 22/01/2007 Hi Don Post an example of your source data Try showing exactly what you have in each of your 4 columns  SignatureRegards Roger Govier > Hi Group, > [quoted text clipped - 56 lines] > 21/01/2007 > 22/01/2007 Hi Roger, I think the four columns might be clouding issue they are similar repeats of shift pattern as already shown, copied below anyway. If I can get this to work I plan on using an IF function to select my sum range IF A1=Red C2:C500 for example. I think my problem is being able to set the range of my SUMIF from the date first sick to date fit. I'm trying to get multiple citeria to work in sumif without success and am looking at newsgroup archives of sumproduct, as yet without success. If you need me to add more please say Red Green Blue White 01/01/2007 D1 1 R3 R1 N1 1 02/01/2007 D2 1 R4 R2 N2 1 03/01/2007 N1 1 D1 1 R3 R1 04/01/2007 N2 1 D2 1 R4 R2 05/01/2007 R1 N1 1 D1 1 R3 06/01/2007 R2 N2 1 D2 1 R4 07/01/2007 R3 R1 N1 1 D1 1 08/01/2007 R4 R2 N2 1 D2 1 09/01/2007 D1 1 R3 R1 N1 1 10/01/2007 D2 1 R4 R2 N2 1 11/01/2007 N1 1 D1 1 R3 R1 12/01/2007 N2 1 D2 1 R4 R2 13/01/2007 R1 N1 1 D1 1 R3 14/01/2007 R2 N2 1 D2 1 R4 15/01/2007 R3 R1 N1 1 D1 1 16/01/2007 R4 R2 N2 1 D2 1 17/01/2007 D1 1 R3 R1 N1 1 18/01/2007 D2 1 R4 R2 N2 1 19/01/2007 N1 1 D1 1 R3 R1 20/01/2007 N2 1 D2 1 R4 R2 21/01/2007 R1 N1 1 D1 1 R3 22/01/2007 R2 N2 1 D2 1 R4 23/01/2007 R3 R1 N1 1 D1 1 24/01/2007 R4 R2 N2 1 D2 1 25/01/2007 D1 1 R3 R1 N1 1 26/01/2007 D2 1 R4 R2 N2 1 27/01/2007 N1 1 D1 1 R3 R1 28/01/2007 N2 1 D2 1 R4 R2 29/01/2007 R1 N1 1 D1 1 R3 30/01/2007 R2 N2 1 D2 1 R4 31/01/2007 R3 R1 N1 1 D1 1 01/02/2007 R4 R2 N2 1 D2 1 02/02/2007 D1 1 R3 R1 N1 1 03/02/2007 D2 1 R4 R2 N2 1 04/02/2007 N1 1 D1 1 R3 R1 05/02/2007 N2 1 D2 1 R4 R2 06/02/2007 R1 N1 1 D1 1 R3 07/02/2007 R2 N2 1 D2 1 R4 08/02/2007 R3 R1 N1 1 D1 1 09/02/2007 R4 R2 N2 1 D2 1 10/02/2007 D1 1 R3 R1 N1 1 11/02/2007 D2 1 R4 R2 N2 1 12/02/2007 N1 1 D1 1 R3 R1 13/02/2007 N2 1 D2 1 R4 R2 14/02/2007 R1 N1 1 D1 1 R3 15/02/2007 R2 N2 1 D2 1 R4 16/02/2007 R3 R1 N1 1 D1 1 17/02/2007 R4 R2 N2 1 D2 1 18/02/2007 D1 1 R3 R1 N1 1 19/02/2007 D2 1 R4 R2 N2 1 20/02/2007 N1 1 D1 1 R3 R1 21/02/2007 N2 1 D2 1 R4 R2 22/02/2007 R1 N1 1 D1 1 R3 23/02/2007 R2 N2 1 D2 1 R4 24/02/2007 R3 R1 N1 1 D1 1 25/02/2007 R4 R2 N2 1 D2 1 26/02/2007 D1 1 R3 R1 N1 1 27/02/2007 D2 1 R4 R2 N2 1 28/02/2007 N1 1 D1 1 R3 R1 01/03/2007 N2 1 D2 1 R4 R2 02/03/2007 R1 N1 1 D1 1 R3 03/03/2007 R2 N2 1 D2 1 R4 04/03/2007 R3 R1 N1 1 D1 1 05/03/2007 R4 R2 N2 1 D2 1 06/03/2007 D1 1 R3 R1 N1 1 07/03/2007 D2 1 R4 R2 N2 1 08/03/2007 N1 1 D1 1 R3 R1 09/03/2007 N2 1 D2 1 R4 R2 10/03/2007 R1 N1 1 D1 1 R3 11/03/2007 R2 N2 1 D2 1 R4 12/03/2007 R3 R1 N1 1 D1 1 13/03/2007 R4 R2 N2 1 D2 1 14/03/2007 D1 1 R3 R1 N1 1 15/03/2007 D2 1 R4 R2 N2 1 16/03/2007 N1 1 D1 1 R3 R1 17/03/2007 N2 1 D2 1 R4 R2 18/03/2007 R1 N1 1 D1 1 R3 19/03/2007 R2 N2 1 D2 1 R4 20/03/2007 R3 R1 N1 1 D1 1 21/03/2007 R4 R2 N2 1 D2 1 22/03/2007 D1 1 R3 R1 N1 1 23/03/2007 D2 1 R4 R2 N2 1 24/03/2007 N1 1 D1 1 R3 R1 25/03/2007 N2 1 D2 1 R4 R2 26/03/2007 R1 N1 1 D1 1 R3 27/03/2007 R2 N2 1 D2 1 R4 28/03/2007 R3 R1 N1 1 D1 1 29/03/2007 R4 R2 N2 1 D2 1 30/03/2007 D1 1 R3 R1 N1 1 31/03/2007 D2 1 R4 R2 N2 1 01/04/2007 N1 1 D1 1 R3 R1 PS, I've got this to work for a single date criteria IF(P1="Red",SUMIF(B4:B34,">="&M1,D4:D34),"etc") and just need to add <= but don't know how Regards Don Hi Don I'm still lost. What I see are 7 columns, not 4 There is a Date column - is this column A? Then taking the first row of data only I see A2 01/01/2007 B2 D1 C2 1 D2 R3 E2 R1 F2 N1 G2 1 Which column is supposed to be represented by Red, and Green and Blue and White? I am certain we can resolve your problem with Sumproduct (or even with a Pivot Table) but as Yet I am not understanding what it is your are trying to achieve. If you would prefer, you could send me a copy of your file with explanation, direct. To send direct roger at technology4u dot co dot uk Do the obvious with at and dot. SignatureRegards Roger Govier > PS, I've got this to work for a single date criteria > [quoted text clipped - 5 lines] > > Don |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.