Tracker
HOME    TRACKER    Excel

# Average Number Of Days In A Quarter

## I have a worksheet containing 5 columns. A start and end date, a number of days between these dates, the quarter number of the start date (i.e. 1 to 4) and then the year. I want to calculate the average number of days per quarter so an average of days in column C, based on the details in column D and E.

Related Forum Messages:
Average Number Of Days
I would like to find the average number of days events take from start to finish.

My data is in 3 columns:

EventName StartDate EndDate

I have a ton of EventNames, each of which have a StartDate. But I want to calculate the average days to complete only for those EventNames that have EndDates.

So, if a cell in the column EndDate is greater than zero, then take that cell, subtract it from the corresponding cell in the StartDate column; Add all of them together and divide by the count of those rows in the EndDate column that have a value.

How do I put this in excel terms? ....

Formula To Return The AVERAGE By Calendar Quarter
I'm trying to write a formula to return the AVERAGE by calendar quarter, IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Identify Higher Earnings In A Quarter And Return The Max Of The Quarter
I'm trying to figure out how to identify higher earnings in a quarter and return the max of the quarter of time frame and not the value

I'm simplifying a little ...

Calculate The Quarter To Date Number
I was given the formula below to calculate the quarter to date number. The months from 1/1/05 thru 12/31/07 are in cells C3-AL3.

First - can anyone explain the formula so I may understand it and possibly modify it for other uses

Second - wherever the formula has 3:3 and I copy it to the next cell below it, I have to change the 4:4 to a 3:3 - any suggestions on how to do this easily.

By the way, the formulas are in column AO, staring on row 4.

=SUM(OFFSET(A3,1,MATCH(DATE(YEAR(Date!\$C\$6),INT((MONTH(Date!\$C\$6)+2)/3)*3-2,1),3:3,0)-1,1,MATCH(Date!\$C\$6,3:3,0)-MATCH(DATE(YEAR(Date!\$C\$6),INT((MONTH(Date!\$C\$6)+2)/3)*3-2,1),3:3,0)+1))

Average A Range IF Within 30 Days
I have a spreadsheet with dates running down column J, and a number running down column K. I want to average the numbers in column K for all the rows that are less than 30 days old.

I have a formula for determining if a date is less than a month old:
=IF(DATEDIF(J2,TODAY(),"d")<31,TRUE,FALSE)

How can I translate that into averaging a range?

I'm using Excel 2003

Average For The Past X Days
I want to keep track of housekeeping attendants' average productivity of rooms cleaned per day/shift for the last 10 days worked.

I need a formula which will give me an average of the 10 most recent cells with a value (0 should be counted).

I also want to have each month in a separate tab, and for the formula described above to average between two tabs should the 10 days cross-over.

Count If Formula: Count The Number Of Nhew Stores Each Quarter
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....

Find Out How Many Ticks Were On Green Days, Amber Days And Red Days
I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.

Number Of Days Between 2 Dates
I am trying to set up a function to derive a number of days between 2 dates.

Within the table I have an on hire date, and off hire date, and an optional suspend hire date range in 2 other columns - ie. there are 4 columns with dates in.

On hire is in column B; offhire in C; suspend hire in G and recommence hire in H. The optional suspend hire date range will always fall within the main hire date range.

Column I calculates the number of days for the suspend hire period.

My key cut off date is in cell C4.

There are 3 different scenarios I need the function to cover.

1 - if the cut-off date (in C4) is later than recommence hire, calculate the number of days from hire start to cut-off date, less suspend hire period days.

2 - if the cut-off date (in C4) falls within the suspend hire period, calculate the number of days from the hire start to the begining of the suspend hire period.

3 - if the cut-off date (in C4) falls before the suspend hire period, calculate the number of days from the hire start to the cut-off date.

This is the fuction I have written:-

=IF(\$C\$4>H10*(\$C\$4-B10-I10),
IF(AND(\$C\$4>G10,\$C\$4

Number Of Days Per Year
If I have a year in a cell, say 2006, and in another cell I need a formula to tell me how many days are in that year. Just to try and calculate deliveries of things far into the future, so need to account for leap years.

Counting The Number Of Days
1/1/2007
1/1/2007
1/2/2007
1/2/2007
1/2/2007
1/30/2007
1/30/2007
1/31/2007
1/31/2007
1/31/2007
1/31/2007
2/1/2007
2/2/2007
2/2/2007
2/2/2007
2/28/2007
2/28/2007
2/28/2007
2/28/2007
2/28/2007

This list goes on with every workday of the month repeat several times, with no day have any set number of entries. Also each new month is just tacked on to the previous list. All of this is in column A but there are many other columns of data with these dates. I just didn't see the need to replicate that here.

What I need is to be able to count the number of unique days per each month but I do not want to have to specify a range for the month of January, then another range for February, etc. I just want to be able to list the column A4:A1200 and have the formula select just the unique days for January. Then in another cell modify the formula for unique days in February and so on.

Find The Number Of Days
If I was to say have a date in cell A1, is there a formula which would tell me how many days from that date left in the financial year?

I was thinking an IF statement, but I know thats way off course! And it could get quite messy.

This is what I would like it to do:

Cell A1 - 30/10/2007

Formula - Finds the number of days between 30/10/2007 and the remainder of the financial year, which for me ends on 30/06/2007

Should give me the answer of "x" amount of days.

Calculating The Number Of Days
If I had two dates in two separate cells , so E2 is the 01/10/08 and F2 is 06/10/08 and I want to work out that their is a difference of five days what would the sum be? Also is there anyway I could factor into that sum what is pure working days as opposed to weekends?

Number Of Days Between Two Dates
I couldn't find a solution to my problem in the forum: Is there are formula that will allow me to calculate number of days between two dates? The date format is eg. 07/06/07

Calculate Number Of Days Between 2 Dates
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

Count Number Of Days Between 2 Dates
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.

In Dates Count The Number Of Days
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?

Number Of Days In Month VBA
Im trying to use the equivilent of: =DAY(DATE(YEAR(H5),MONTH(H5)+1,0)) in vba. but i am having trouble with the use proper parenthesis.

basically the above will work in an excel workbook and return the number of days in a month. thats all i really need. so if there is an easier way, which im sure there is, then im up for it...

Number Of Consecutive Days And First Occurrence
In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.

The values in column BN are text with either of those names.

I need to return two values in cells BO7 and BO8 respectively:

1) The number of consecutive days back from the most current day that have been "Over" the limit

2) The date going from the most current day that the first "Over" occurred

Count Number Of Days Except Sunday
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"?

Number Of Working Days Between 2 Dates
I'm currently working on a file that has employee holidays/sickness etc. What I want to do is be able to see is a DATE FROM and DATE TO and next to it the total amount of working days between and including those dates. On top of the since employee's sometimes like to take half days I need it so that it can determine half days as well.

So for example I have an employee going to be off work from 29/10/2007 until 09/11/2007 12pm which is 10.5 days in total, but that includes weekends when I don't want it to.

Also it comes out as 11 days at the moment because I can't figure out how to get it to determine is it is half a day.

What formula should I use or if someone could create an example that would be really usefull :D

Number Of Days (Sales) Inventory
I am looking to calculate how many days worth of inventory I'm currently holding (in stock and on order from supplier) based on my sales over the past 30 days.

I've seen a number of formulas around... and honestly am not sure I'm on the right track.

On the attached I have used the following:

(Stock on Hand + Stock on Order) * 30 / ( Units of the item sold in the past 30 days)

Formula For Number Of Days In A Month
I have checked searching the forums for this but nothing that really matches what I am looking for. I am trying to, from a date, deduce how many days there are in that month. I have tried a solution where I look at EOMONTH adding and subtracting but this is quite cumbersome. Is there some code out there where someone has solved this?

Working Out The Number Of Days A Job Has Taken.
I have a table with 2 date columns, and I need to subtract one date from another to give me the number of days the job has taken, but this also needs to exclude the weekends. There is no hours involved.

I.e.
Start Date Finish Date Days Taken
22/Jan/2009 27/Jan/2009 4

Is there a formula that i can use to work out the days taken.
there is multiple rows of information, the above is an example.

Calculating The Number Of Days Between Dates
I need a formula that will allow me to put a date in cell a2, and in cell a3 put the the number of days between 2 dates. Example.. For example (A2) shows 08/06/08, a3 to show the number of days from 08/06/08 to 08/14/2008--(a3 )14 and (a4) to shows the number of days from 08/06/08-10/05/2008 ---(a4) 60 days.

Formula To Get Number Of Passed Days
I have a formula which calculates number of days remaining. But I need a formula to to know how many days passed after a particular date.

Example :-

Date .Days passed
10-Feb-2010 .6

=IF(ISBLANK(G14),"",IF(TODAY() > G14,"LIC EXPIRED",G14-TODAY()))

Whole Number For Days' Difference
I want to determine the days' difference between 2 days, but I keep getting decimals.
(I don't want to foramt the column, because I want to use the value in a Pivot Table.

I've tried:
=SUM(TODAY(),0-G2,0)

Add Number Of Days To Dates
I have a start date (say 01/04/07) and I need to add a number of days to it to get the start of the next financial period. Unfortunately, I need to ignore a small list of dates (bank holidays, etc).

Calculating Number Of Days Between Dates
is there a way to calculate the number of days between two dates using the excel cells

example.....how do i put in formulas so excel will calulate the number of days between May 3rd and september 19?

i want to enter may 3 in say a1 then sept 19 in a2 a3 should says days between em

I'm working on a complex A/R aging summary. So Ill have couple of question, but right now I'm trying to have excel automatically keep track of the A/R process. For instance every business day I would like the number of days the Invoice is outstanding to go up by 1. So when I get to work on monday the invoices that have been due for 20 days will now show 21 without me touching it.

Number Of Working Days Between Dates
I need to work out the number of working days between 2 dates, where the working week is Monday to Thursday, ignoring all bank holidays etc.

Calculate Number Of Days Using 2 Sets Of Dates
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?

Calculate The Number Of Days From Start To End
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...

Number Of Days Worked Over A Time Frame
I need to be able to calculate the actual number of days worked for an employee over a specific date range. I have a data sheet containing employee, transactions, and date of transaction (date only, does not include time). The actual number of days worked may fluctuate due to time off or holidays, so I need to be able to calculate this individually by employee. For example, with columns Employee, Transaction, & Transaction Date:

123 ABC 07/01/2009
123 ABC 07/02/2009
123 DEF 07/02/2009
123 GHI 07/02/2009
123 ABC 07/03/2009
123 ABC 07/07/2009
123 DEF 07/07/2009
123 ABC 07/08/2009
123 ABC 07/09/2009
etc....

Should return 4 work days for employee 123 over the date range 07/01/2009-07/07/2009

360 Days :: What Is The Current Number Of Day?
there are 360 days in a year. is it possible for excel to show what is the current number of day?

eg. today is =350

Represent Number Of Days Into A Scale For Statistics
I have a scenario where by Column A is date incident opened and Colum B is date incident closed.

And Column C shows the days between them. I got this and its fine. However, how do i
make a table or in any how define it as if the number is 1 - 3 days then it should be A

1-3 days = A
4-6 days = B
7-8 days = C
9 days onwards = D

This is because i need to make a chart and graph of the turn around time and the best is to define into a character alphabet so the chart will show the statistics of how many A, B, C and D But how do i do that.

Lookup- Way Of Searching For A Job Number Across All 31 Days
Due to the company that i work for doing alot of manuafacturing each and every day, i need some sort of look up.

For example, the currently excel sheet i use.

[IMG][/IMG]

As you can see i have many different Job Numbers that i use.... BUT

The problem that i am facing is, that these jobs sometimes are carried over to more than one day, [sheet bottom of screenshot] but also we might make the job one day and and then not again untill next week using the same Job Number.

What I Need
I need a way of searching for a job number across all 31 days and for it to total up the "Quantity (Qty)" of them all that we made upon that Job Number.

So lets say the Job Number 91294 was run on day 1 (shown in screenshot).
But it then ran again 3 days later on the same Job Number 91294.

I need it to be able to work that out the total for me across them days being 45 upon the Job Number 91294.

Things to Consider
The layout will NOT change, no extra rows or coloums will be added.

Calculate Number Of Days From A Date Field
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.

Calculating The Number Of Business Days In A Specified Period
It should be deplaying dates of weekly days in Monday, Wednesday and Friday excluding sundays. Or Tuesday, Thursday Saturday excluding Sunday. e.g

Mondays, Wednesdays and Fridays of June
June 1, 2006
June 3, 2006
June 6, 2006

Tuesdays, Thursdays and Saturdays of June
June 2, 2006
June 5, 2006
June 7, 2006

This should be happening after entering any date on the first cell of the List and should accommodate up to 3 months

Conditional Formatting For The Number Of Working Days
I am trying to do a conditional formatting for the number of working days. What I am trying to do is:

Example:

As in the row D7 I give the actual workdays required to finish the task the Gantt Chart gets a specific formatting on the right appears for the number of working days.

Problem:

I want to avoid the formatting if there are any weekend (Saturday or sunday)

Suppose I give number of working days as 20 then there would be 6 weekend days so it shall add to the overall actual working days but again sinnce we are adding 6 additional days one more weekend day would come in between so the formatting shall take care of this.

Count Number Of Dates Older Than 30 Days
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.

Subtracting Early Dates To Get A Number Of Days
I wish to subtract the date 11/18/1877 from 11/16/1968 to get a result showing the number of intervening days but excel does not want to do it.

When I take 11/18/1900 from 11/18/1968 and format the result to get a number with no decimals the result is 24835 as it should be; but it does not seem to work for dates earlier than 1/1/1900.

=WORKDAY But Includes Weekends In The Specified Number Of Days
I'm looking for a function similar to =WORKDAY but with a slight variation.

I want the formula to return the date that is the closest non-weekend, non-holiday day that is a specific number of CALENDAR days from the start date. So the formula does not return weekend or holiday days, but does include them when counting the days away from the start date.

For example if the start date was Dec 1st, and I specified 14 days after this date, I would want Dec 15th returned (as the 14th is a weekend).

Using Countif To Calculate The Number Of Days In A Column
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.

Expressing A Number Into Days-hours-minutes
I'm hoping someone here might be able to help me please?

I am trying to write a function that will convert a number into days-hours-minutes. I have managed to get as far as hours-minutes using the following function.

=INT(A1/60)&"h "&ROUND(MOD(A1/60,1)*60,0)&"m"

e.g. If A1 = "7090" the result of this function will be "118h 10m".

I now need to express the result as xxxd xxxh xxxm and this is where I am stuck!

Determining Number Of Specific Days In Period
I am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range. For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.

Calculating Number Of Days Between A Choice Of Dates
I have a spreadsheet where I have an intial date of issue of a warrant. Once this warrant has been executed, then a further column is populated with the date. The data I need to work out (Preferably without using macros if possible) is, if there is no execution date the length of time from issue to todays date, but if there is an execution date then length of time from issue to execution. I can do either on their own, but i would like to have only one column showing this info and if possible to update each time the sheet is opened

Count Number Days Excluding Weekends
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

Calculating Number Of Days For Job Length Periods
I'm trying to calculate the length of a work order to develop an average and future proposal estimates.

For example:
In work = B1
Job completed = C1
Total days to complete = D1

I know I can enter in D1 C1-A1 and get the correct result but I want the field to calculate A1-TODAY() until a completion date is entered. This would display the number of days the job has been in work until completed. Once completed use the C1 for calculation.

Sorta like
If(C1=">=0" then (A1-Today()) else C1-A1)

I will then use conditional formatting with block colors to indicate whether it is a completed job or in work job.

Formula Required To Calculate Number Of Days Remaining
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.