# Conditional Count (calculate Number Of State Holiday Days)

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
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
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
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
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 29, 2014

I am trying to calculate the number of days between two dates where the arrival date is in b5 and the departure date is in c5 for each month. I have been using the following formula =IF($C5="","",MAX(0,MIN(L$1,$C5)-MAX(K$1,$B5))) but when the stay is a full year it is giving me 365 in a single column. I think maybe I need to add in the number of days in the month but not sure how to do it.

View 2 Replies
View Related
Apr 28, 2013

Our company has been taken over by new mgt (Y co) on 1st Nov 2012 from old mgt (X co). Y co has the policy of providing employees with EOS benefits as mentioned below:

1st 5 yrs - 1 month salary per year

5-10 yrs - 1.5 month salary per year

+10 yrs - 2 months salary per year

But for deciding the slab, total period of service is considered. The below table provides the calculation of days as on 31st Dec 2012 based on different joining dates which I need to fix by way of formula.

Name

Hire date

Salary

Total service (days)

X co

Y Co

1st 5 yrs

5-10 yrs

+10 yrs

George

09-02-2005

1000

2883

2822

61

61

[Code] ........

View 4 Replies
View Related
Sep 4, 2008

I have 2 dates a Start Date and an End Date.

I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column.

Start End Yr1 Yr2 Yr3

11/01/99 11/01/00 11/01/01

10/31/00 10/31/01 10/31/02

Col A Col B Col C Col D Col E

10/31/00 07/05/01 365 117 0

I thought a series of IF statements but I am open to an easy solution...

View 9 Replies
View Related
Jul 28, 2009

I have a start date formatted as dd/mm/yyyy in cell A5

I have an end date formatted as dd/mm/yyyy in cell E5

In cell F5 I would like the result of the number of days between E5-A5 (Excluding weekends)

In cell G5 I would like the total number of days in the range F5:F25

In cell H5 I would like the total number of working days from A5 to Today

I am using the following formula to get the result in F5 etc

=SUM(INT((WEEKDAY(A5-{2,3,4,5,6})+E5-A5)/7))

I have copied this down and in the blank cells I get #NUM!

In cell F? were there is no date entered in cell E? I get a minus number (which I can hide using conditional formatting), but the minus number obviously has an affect on the result in G5, as cell G5 gives me #NUM! instead of the result for the current numbers in row F

View 9 Replies
View Related
Jul 7, 2013

How can i get the actual Payment due date from no. of days using macro. i. e.

Posting Date Dues Payment Date

------------ ------ ---------------

22.06.2013 21 days ???

View 9 Replies
View Related
May 18, 2006

I need to calculate on a spreadsheet the number of days between a specific date and the date the spread sheet is opened. I need to know the number of days a merchandise has been stored.

View 5 Replies
View Related
Dec 11, 2013

I'm looking for the formula to calculate the number of winter days between two dates.

Say, that I'm having:

- Winter season start date (say Dec 1st)

- Winter season end date (say, March 1st)

=> All the days which are between these two are considered winter days for any given year.

- Two different dates defining the beginning and the end of my PERIOD. The total length of the period may be more than one year.

HOW do I calculate total number of winter days for the PERIOD?

View 13 Replies
View Related
Mar 17, 2014

I have a list of months in column A and would like a formula in column B that calculates the number of calendar days for each month (i.e. Jan=31, Feb=28 etc.)

View 9 Replies
View Related
Jun 4, 2009

I'm new here. I've searched these forums in the past for help, but never had to post, i've always been able to find something by searching. However, this time I couldn't find anything that addressed my specific need so I figured it'd be worth a shot.

Alright, I have two sets of dates, and i'm trying to set up a formular that calculates the numbers of days from the first date range that overlap/fall within the second date range.

For example:

In A3 type "1/1/2006"

In B3 type "1/1/2007"

In C1 type "6/30/2005"

In C2 type "6/30/2006"

Now, in C3 i'd like to insert a forumla that tells me how many days in the date range of 1/1/2006-1/1/2007 fall in the date range of 6/30/2005-6/30/2006. Basically for this is should be half the year, however many days that is.

Is this possible with a formula?

View 10 Replies
View Related
Dec 16, 2009

I am trying to use the following formula to count the number of times a day appears in a column. It doesn't seem to be working the way I want it to. =COUNTIF($A13:$A200,"Sunday"). It could be the way the days of the week are gathered on the sheet, you can see the attached workbook to see how I am using it.

View 4 Replies
View Related
Sep 28, 2011

Any example of counting the # weeks/days between two dates?

View 1 Replies
View Related
Oct 7, 2011

I need to calculate the number of days between 2 dates including holidays and weekends. That means I cant use NETWORKDAYS as it excludes weekend.

View 9 Replies
View Related
Apr 1, 2014

Need to calculate the number of days in December between two dates 11/20/2013 and 1/20/2014, how many days are in december? a formula that can calculate this?

View 9 Replies
View Related
May 7, 2014

In cell A1 = 15/06/2006 (dd/mm/yyyy)

What is the formula to find how many no. of days in month provided in cell A1.

View 2 Replies
View Related
Feb 6, 2009

I need to have a cell with a date in it then the next cell needs a formula to caluclate how many days it has been since that date???? I'm a real novice with excel.

View 9 Replies
View Related
Feb 3, 2014

I have been using Excel to display, sort and present data in a professional way. I have created a pivot table with two columns below. Column C indicates the start date. Column D indicates the completion date. Blank cells show live data. I need to determine the number of days. 1) That have been - completed - a) That were < 2 Days, b) >2 but less than a week, c) > than a week (all). I have used the following formulas. a) countif(D4:D61, "<3"), b) countifs(D4:D61, ">2", D4:D61, "<=8"). Easy for completed data. My problem is, how do I determine the number of days, live data that following the same queries as above. I have figured out I can use countif to count the number of blanks by - countif(D4:D61, "(blank)") but how to put it all together and what formula should I use. I am trying to use countifs or if statements but am totally lost.

Column C Column D

---------- ----------

06/01/2014(blank)

25/11/2013(blank)

13/12/201314/01/2014

27/12/201317/01/2014

27/12/201310/01/2014

View 2 Replies
View Related
Feb 21, 2014

I'm trying to create a new column which calculates the number of days from the today's date to the due date of an invoice. Also a Column that an invoice is/was overdue.

Column A - Clerk inputs date that invoice was received

Column B - Clerk inputs the date that the bill was paid

Column C - Auto populates the due date of the invoice (20 days after the invoice is received)

Column D - I want a number of days to auto populate based on today's date that will show how many days we have to pay the bill. Example: today is 2/19/2014, bill is due 2/22/2014 (Column C), column D should read 3 (I would prefer is the number is black for "we have days left to pay", red for "we're behind")

*Extra bonus for Column D, if the column goes blank after a date is entered into Column B* - but not necessary

Column E - I would like if the date the bill was paid (Column B) is greater than the date the invoice is due (Column C) to show "Overdue" in the cell.

View 9 Replies
View Related
Mar 19, 2014

I have two date columns, a start date and end date. Another columns tells me the number of days between these two columns. It may return 45 days which actually straddle 3 different months.

My aim is to identify between this date range how many days in Apr 14. Then in the next column howmany days in May 14 etc

So on the spreadsheet Id have 12 columns for each month of the year. in Apr 14 for each row between the given date range I want it to tell me how many days are actually in April. Id then copy that formula into the May 14 column.

View 1 Replies
View Related
Apr 1, 2009

A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.

A1 B1 C1 D4

2 4 6 C1-Formula is: =sum(A1+B1)

Now I have to write formula for D4-cell:

i.e. =sum(C1/Total days remaining of the month)

Suppose Today is April 01, 2009, C1/should be devided by 30,

Tomorrow will be April 02, 2009, C1/should be devided by 29.

View 10 Replies
View Related
Nov 26, 2009

I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact? For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.

View 4 Replies
View Related
Oct 16, 2009

I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.

View 19 Replies
View Related
Jul 5, 2007

I'm trying to calculate the number of days between dates in column A and B. I've looked at the examples in this site and thought I used the formula correctly, but the cell returns an error message when I type: =DATEDIF(A1,B1,"D")

View 7 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