# Sumproduct Query: Count Number Of Days Holiday

Jun 29, 2009
I have a formula which I have copied from one of your good selves and tried to adapt it. The formula is below. I a am getting #NA as my answer.

=SUMPRODUCT(ISNUMBER(FIND({"h","H"},B11:G11))*{0.5,1})

I have a person name in Colomn A and then 1 week B-G on a row and want to count number of days holiday.

View 4 Replies
ADVERTISEMENT
Mar 14, 2014

I am making process TAT(Turn Around Time) which required following information. In Excel 2007.

1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)

A1-Start Date Mar/01/2014

B1-End Date Mar/31/2014

C1-No Of Days 22

D1-Days between two dates 21

E1 To E10-Holidays

2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)

A1-Start Date Mar/01/2014

B1-End Date Mar/31/2014

C1-No Of Days 27

D1-Days between two dates 26

E1 To E10-Holidays

Note : Any weekend (off days) dates listed in holidays should not effect the query.

View 9 Replies
View Related
Jan 19, 2010

I m trying to do is this. For every person in company there is a table 30 days (cells) long, and 3 shifts (cells) wide. Every shift leader fills in the data in the "calendar" so every day, for every person, he will fill in number of hours.

I have to make excel process the whole calendar 30 x 3 = 90 cells, into final results for our payroll department. So the end product of the excel table should be Person name, total work days, total sick leave days, holiday days, and so on.

specific problem i have is how to calculate number of State Holiday days. You can see my best attempt in the attached file.

if there is only one number / mark entered each day (in only one of the 3 shifts) thansumproduct works ok.

View 8 Replies
View Related
Mar 17, 2009

I have a question, does anyone now how to count between dates, for exemple :

01-02-2009

15-02-2009

22-02-2009

25-02-2009

03-03-2009

11-03-2009

And i wont only to count between 01-02-2009 / 28-02-2009, the result it will be 4.

View 14 Replies
View Related
Nov 26, 2013

Create a formula to count number of days an employee work (in 25 working days and 5 Friday as rest day month):

Example:

If employee works 25 days during weekdays he will get counted as = 30 days

If employee works only 22 days during weekdays with 3 absences during weekdays he will get counted as = 27 days.

If employee works from 1st to 24th and 25th to 30th absence he will get counted as = 24 days.

Other examples and output desired I attached here: Sample.xlsx

View 4 Replies
View Related
Mar 28, 2014

Need a formula (E1) to determine the number of days that have passed between two given dates (C1 & D1) ONLY when the Status of a given report (B1) is "Late"

B1 = Status (Pending; On Hold; Late; Completed) (i.e "LATE")

C1 = Status Date (i.e. 28 Mar 14)

D1 = Deadline (date) (i.e. 25 Mar 14)

E1 = Total Days Late (= ?)

Note: I'm using Office for Mac 2011

View 1 Replies
View Related
Jan 5, 2007

I have a range of dates. i would like to count the number of days that are earlier than the date =TODAY().

Do i use the COUNTIF function?

View 9 Replies
View Related
Jan 6, 2009

In B13 I have the start date: 12/1/2008

In C13 I have the end date: 1/5/2009

In cell D13 I want a formula that counts the number of days between the two dates that are not Sunday. The start and end dates are included in the count.

I have created this formula using Ctrl + Shift + Enter:

{=SUM(IF(WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)7,1,0))}

I have also created this formula using Ctrl + Shift + Enter:

{=SUM(IF(TEXT(ROW(INDIRECT(B13&":"&C13)),"ddd")"Sun",1,0))}

They both seem to work. I get a result of 31.

Is there a formula that is better than this, more efficient than this, or "less expensive"?

View 9 Replies
View Related
Nov 5, 2009

=SUMPRODUCT(SLVL!$B$6:$B$49>=$A$2)*(SLVL!$B$6:$B$49<=$B$2)*(SLVL!$A$6:$A$49=A12)

I'm sure that there had been an error somewhere but I can't figure it out. I'm getting zero value. Situation: need to count number of leaves within a specified period, thus I opted to use Sumproduct.

View 4 Replies
View Related
Jan 23, 2014

I need to count the actual number of days between 2 dates but excluding Sunday...

For example,

Start Date-----End Date-------# of Days

01/Jan/14-----23/Jan/14-------20 days (excluding Sunday)

In some cases, Holiday also to be removed i.e. 01-Jan-2014 is holiday, then the # of days should be 19 days.

If I use Networkdays formula, then system results excluding both Saturday and Sunday. But I want to remove only Sunday.

View 5 Replies
View Related
Dec 21, 2012

If a cell contains a birthdate, how do I count the number of days till the next birthday? The date is entered, as an example "1/1/2013".

View 5 Replies
View Related
Nov 12, 2008

I need to figure out how to tell Excel to count the number of invoices on a worksheet older than 30 days and display that number in my Summary worksheet. Here's a quick example:

Worksheet '1234' has 10 invoices, 6 of which are more than 30 days old (invoice dates are in column B). Worksheet 'Summary' needs to display the total number of invoices and the number older than 30 days.

View 2 Replies
View Related
Jun 9, 2007

I am trying to set up a sick leave list which will work out the cumulative total for the last 12 months (from last day of last sick entry). The problem I have, as the is is added to, I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to the last date. Hope this is clear. The next problem is I need it to work out total days and then without weekends. Below is the chart I have to work out.

Column 1 Start date of sickness

column 2 End date

Column 3 the number of days sick in period (column 2 less column 1 in days)

column 4 the number of days sick in period above excluding week ends (column

2 less column 1, less weekends in days)

column 5 total sick days in last 12 months from date in column 2 going back

a year including weekends

column 6 total sick days in last 12 months from date in column 2 going back

a year excluding weekends

What function or formulas can I use to calculate column 3,4,5 & 6. I am a not very experienecd in Excel

View 7 Replies
View Related
Jan 17, 2014

I'm trying to count the number of rows in a dataset where column A is something, B is something, C is something, D is not something, D is not something else, and column E > 0 OR F is something but I just can't get the result I want.

My actual dataset is a staggering 294,000 rows and 46 columns so here's a simplified version:

View 8 Replies
View Related
Nov 3, 2009

I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column.

I have tried using this SUMPRODUCT formula:

=SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones")))

but it is generating incorrect values!

Specifically:

- Jones shoulld return 1

- Franks should return 3

- Smith shoudl return 0

View 4 Replies
View Related
Jan 29, 2013

My problem is , I have a date range 21-Feb-2013 till 07-Mar-2013 (Col C2, Col D2 respectively) which is holiday list for a person.

I am trying to pull out the number of working days for that person in the month of Feb which should exclude weekends.

To get the total no. of working days for the month of Feb, I have used the below formula.

=NETWORKDAYS(DATE(YEAR(C2),MONTH(C2),1),EOMONTH(C2,0))

This gives me a value of 20 which is correct.

Now I need to find no. of days which fall under the month of FEB from the date range 21-Feb-2013 till 07-Mar-2013 (Col C2, Col D2) which are working days. so that I can subtract that from no. of working days for that month (FEB) to get the no. of working days which the person has actually worked.

View 3 Replies
View Related
Aug 4, 2008

I have been using a formula to count the number of days between 2 dates and if the date was older than 12 months it would work fine.

The data that i was using has been changed and they have decided to change the format of the dates and dropped the year reference no all i have to work with is dates like 27-07 this represents 27th July i can format this date to work fine unless the date falls before 1 jan this year all i get is lots of numbers and it stuffs up the whole calculation.

The formula i am using to compare the dates is

=DATEDIF(N31,$D$1,"d")

View 9 Replies
View Related
Aug 9, 2013

I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.

Employee Name

Date:

1/1

1/2

1/3

1/4

1/5

1/6

1/7

1/8

1/9

1/10

[code]....

We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.

Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!

View 2 Replies
View Related
Jan 28, 2010

I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.

I should also point out that most company names appear multiple times in column [C7].

a) Count the unique company names in column [C7]...

b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records

c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records

Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.

View 2 Replies
View Related
Oct 8, 2009

I have three companies in column A, In Column B I have a date of when an item was sent to them, in Column C I have the amount of days this item has been out with that company. Is there a formula that will count the number of days an item has been out with one of the companies per month, and find an average if more than 1 item has been sent per month. Two items have been sent to company A in Sep 09, one item has been there 10 days the other 20 days, the answer I would be looking for is 15.

View 4 Replies
View Related
Jan 23, 2012

I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. Any way to identify the number of days, per calender month, that falls in a date range.

sample data...

Start Date

End Date

Old Value

New Value

08/03/2010

18/06/2010

16758.2

16758.1

[Code] .......

I need to break down the total number of days per month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

24

30

31

18

[Code] ......

As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...

I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.

(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it).

View 8 Replies
View Related
Jan 23, 2009

I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.

View 2 Replies
View Related
May 27, 2012

There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.

Countif Today()+90

View 5 Replies
View Related
Feb 14, 2008

a calculation that has some conditions. I have a spreadsheet where I am calculating how many cells fall within a month date range using forumla :=SUMPRODUCT(('Project Team Actions'!J8:J525S2)) - as it calculates via other worksheets, but I only want it to count the cells that have OPEN or On Going in a neighbouring column, but not count the items that are closed.

View 9 Replies
View Related
Oct 2, 2008

I need to calculate the number of days chargeable in a given month for a tenancy as follows:

eg Current Month 30 Sep 2008

Begin End No of Days in Month

Tenancy 1 01 Jul 2008 31 Jul 08

Tenancy 2 01 Jul 2008 24 Sep 08

Tenancy 1 01 Jul 2008 31 Oct 08

Tenancy 1 01 Sep 2008 30 Sep 08

Tenancy 1 01 Sep 2008 31 Oct 08

Tenancy 1 01 Oct 2008 31 Dec 08

View 11 Replies
View Related
Sep 28, 2011

I need a formula to count how many days hours & mins between 2 dates but included in the formula need to ensure it only counts business days.

Column A 02/09/2011 13:00

Column AO 02/09/2011 13:02

I need in Column AP to show 0 00:02

The formula I have tried using is - =IF(AO3="","",AO3-A3) which doesnt work as instead of showing 0 00:02 it shows 1 00:02. I dont want it to count as 1 day just 2 mins.

Once that has been worked out I need to include WORKDAYS formula to only count working days. I have created the list on 'Controls' Tab Column A10:A47.

merge these together to create the one formula?

View 6 Replies
View Related
Nov 22, 2013

I want to calculate the end date of my German courses. This is how it works:

A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.

Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.

Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.

The workday function on excel cannot do this and I do not have any programming skills to work with VBA.

Legend:

*LU = lesson units; 1 LU is 45 minutes

Holidays:

28-Nov-13

29-Nov-13

08-Dec-13

09-Dec-13

25-Dec-13

31-Dec-13

01-Jan-14

[Code] ...........

View 7 Replies
View Related
Jul 30, 2007

Is it possible to make an if statement in a cell that only adds the cells in a collum if they are not struck through.

I use this for when an order is complete and would like my total at the bottom of the column to change when I strike out an order in the list.

View 9 Replies
View Related
Mar 22, 2014

How to create a spreadsheet with what I think will be a very simple formula?

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.

If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.

If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.

If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.

If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

OR

Another, maybe simpler, way of saying it is:

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.

If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.

If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.

If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.

If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

View 9 Replies
View Related
Dec 5, 2008

the attachment is a sample spreadsheet of what i am trying to do. I would like to create a pivot table so that for each site I can list all of the organic suites individually. Then show a count of only 1 for each site, even when there are several counts for each site. And then for this to be related to a cost of each of the suites so that I can get a total cost for each site.

eg:

site name

organic suites suite cost

o1 x

o2 y

o3 z

Total A

etc

View 3 Replies
View Related