Free Microsoft Excel 2013 Quick Reference

Average days per month

I have a massive list of dates in column A in dd/mm/yyyy format (United Kingdom), from which I'd like to write a formula to calculate the average days per month. Is this possible??

Many thanks!


Post your answer or comment

comments powered by Disqus
I need to determine the number of working days per month/per year. Any help is greatly appreciated.

Also,

How does one respond to a reply on any given posting. I'm new to this message board and to Excel and there have been some very helpful responses to some of my postings. I just want to be able to reply to the person who sent the response and thank them.

Hey there,

I hope one of you can help me on this issue. I would like to calcualte
some financial calculations but I would like to have the formulas
(PV,PMT,IRR etc) use a predefine fix number of days per month.

In other words if I calculate PMT I would like to have an option of
defining either 30 or 31 days per month.
Any Ideas?

--
iboock
------------------------------------------------------------------------
iboock's Profile: http://www.excelforum.com/member.php...o&userid=16013
View this thread: http://www.excelforum.com/showthread...hreadid=274789

Hi

i'm trying to find a way to distribute days per month between 2 dates

I have found a great exemple that should to de trick but there is still a problem left in it.

it gives in the next year (this case '08) an +31 value and a negative value

anyone has an idea how to adjust this?

Thx
Tom

Hi,

is there a way of working out the amount of basic working days( Mon to Fri )
per month in a formula or VBA? i am trying to create a yearly summary for 10
men. this needs to work out the amount of actual working days per month
multiplied by hours to get an accurate amount of actual required working
hours per month.

help greatly appreciated.

Nigel

Hi, sorry if this has previously been posted and solved but the search funtion doesnt work on my browser at work.

I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. im currently trying to figure out a 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 19/06/2010 04/08/2010 16758.2 -224147.3 05/08/2010 17/01/2011 16758.2 2923.1

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 12 31 4 27 30 31 30 31

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)
can any one help at all please?

Ta
Ben

In Excel 2007, what is the simplest formula to use to calculate a rolling sum of the days per month?

My spreadsheet has the columns 1/31/08, 2/29/08, 3/31/08, etc. I'd like to insert a formula that would return 31 in the row below January, 60 at the end of February, 91 at the end of March, etc. Ending in 366 at 12/31/08.

I'd like to avoid using EOMONTH.

Thanks.

I have an Excel spreadsheet with two columns. Column 1 contains a date e.g.
01 Jan 2005, column 2 contains a number.
The rows are organized by date. I can have variable number (rows) of days
per month ... but all days are "bunched together"
I would like to either create a new sheet where I have the days summarized,
in other words I want one row to contain 01Jan2005 with the total of column 2
another row with the total of 02jan2005 etc.
I have been trying to figure this one out. Any help would be greatly
appreciate it.
Thank You!

Hi,

I am putting together a sheet where we are looking to get users to estimate the number of days to do a task for each week, i.e. W/c 17/11 = 5-days, 24/11 - 5 days, 1/12 - 5 days.

And then I need to provide an output of this as days per months, i.e. November - 10; December - 5.

Of course the start and end of weeks don't tie up witht the start and end of months so I have to be a little clever. I have started with an idea of using an intermediary sheet to list all of the days individually and then do a countif against the dates to pull back the number of days in each month.

But this is going to be unwieldy and also the number of columns in a sheet is going to limit the amount of time that users can enter the effort for, i.e. 254 columns = 254 days = 36-weeks.

Is there a way to use an array formula or something similar to do this calculation?

Thanks,

Martin

Please also refer to thread "Quick date calculation functions"

--------------------------------------------------------------------------------
I am using the below function from this thread:

=4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+2))+35)>7)

which will give the number of Tuesdays in a month.

For a different weekday, change the '2' near the end accordingly:

1:Monday 7:Sunday etc etc

Can anybody please assist on how I can include a start and end date for this function.
ie I have set up a count of each specific day per month for Feb2010 to December 2011.
How do I then implement a specific date into this eg I only want the count of each specific day in each month starting at the 13th Feb, and ending at 22nd December.

Also, I have just joined with '+ signs' 7 of the above functions for days 1 through to 7, creating a formula that takes up 5 lines in the formula bar. Is there a basic way for me to do a "For days 1:7, do (xxxxxxxxxxx) "Above formula"

Thanks
Darren

I have an Excel spreadsheet with two columns. Column 1 contains a date e.g.
01 Jan 2005, column 2 contains a number.
The rows are organized by date. I can have variable number (rows) of days
per month ... but all days are "bunched together"
I would like to either create a new sheet where I have the days summarized,
in other words I want one row to contain 01Jan2005 with the total of column 2
another row with the total of 02jan2005 etc.
I have been trying to figure this one out. Any help would be greatly
appreciate it.
Thank You!

can someone plz help me with this:

i have two columns in my excel spreadsheet: Date and Rate
so for each date from jan 1997 till today, i have a rate

i want a new column to calculate the average rate for each month. some months will have 30 days, smoe 31 days, and some 28 days. is there any way to calculate the rates for each month without just manually finding which rows correspond to which month and calculating theaverage of each one.

thx!

In the attached Sheet how can I work out how much of each item is purchased per month and what is the average cost per purchase and unit. I know I can do this using sort and/or filter, but I want it automatically done on a seperate Worksheet for each month. For example - we bought chicken 5 times at a total cost of R1820 which means an average cost of R364 per purchase or R88.706 per unit (kg). If I use sort, once the data is sorted I cannot revert it to the original state.

My existing pivot table has the number of occurances per month. It counts the number of entries per month. One for every entry of the database.

I would like to know the number of occurances per month, and example would be if there are 310 entries for the month of January then there would be an average of 10 per day.

How could I go about doing this?

Hello,
Here's the situation:
Columns: date, daily sales, 20-day running average

We keep 3 months worth of data, but it's all on the same sheet, organized into 3 bordered areas.

So, scrolling down, you see Nov, Dec, Jan, and in the areas there's each day ..

HERE'S THE PROBLEM:

We overwrite the months in a 'rolling' manner. i.e. once Jan is over, Feb will overwrite Nov ... so scrolling down the sheet you'll see Feb, Dec, Jan.

How can I keep the average correct when months have different amount of days in them?

In addition, not every day in the month is listed (weekends), and some days have zero dollars which we don't want to include in the average (I've read about using SUMIF / COUNTIF) ...

If this is enough information to go on, great, if not, let me know what else I can tell you.

Thanks in advance for any assistance!

How do you average a row using only the cells that have data in them? i.e. If you have 31 days per month but only use the cells for 20 of the days and I want to average the data for those 20 days.....

My goal is to have excel take a number, average it over a given number of months, and put those values under the proper date, given a starting date and a length of time.

SO:

I have the following inputs:

Total contract value (e.g. $12,000,000)
Length of contract (e.g. 12 months)
Starting date (e.g. 1/1/00)

And my goal is to break up the revenue recognition evenly per month for the life of the contract.

So for a 1 year contract of $12 mil it'd be $1 mil/month.

My question is this:

How do I give Excel these inputs, and have it automatically populate the revenue per month under the corresponding months - i.e. $1 mil in january 2000, $1 mil Feb. 2000, etc. and then stop once the contract expires (12 months in this example).

Thanks very much!!

Hello there,

can anyone help me solve the error I get when I enter the below given
formula ?

Any help is much appreciated.

Thanks,
Bart

********************************

Bernie,

first of all, thanks for your efforts in trying to find a solution !

When I use the formula though, it comes up with an error. See hxxp://
members.home.nl/hoenb/SNAG-0024.jpg for details on the error.

Many thanks in advance for your tip on solving the error.

Bart

On 30 jan, 17:12, "Bernie Deitrick" wrote:

- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -
> Bart,

> This solution assumes the following:

> Your chasis numbers are in column A, starting in row 3, your entry dates are in column B, starting
> in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column D,
> you have the dates for the first of the month, for the time period that you are interested in: Aug
> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you like...

> Then in cell D3, enter the formula

> =IF(AND($B3 HTH,
> Bernie
> MS Excel MVP

> "Bart" > wrote in message

> ups.com...

> > Hi,

> > I have a sheet with collums for entry date and exit date used in a car-
> > compound. I also have a collumn for each month of the year.

> > What I am trying to do is let excel (VBA) calculate how many days a
> > car was on the compound in every month. This is a monthly/yearly
> > ocurring item to check the invoicing.

> > What I'd like to do is to pull data from our database into the excel
> > sheet, just the chassis number, entry date and exit date. I would
> > like to paste this in my excel, and then be presented with the amount
> > of days a car was on the compound in each month.

> > Is there any way to this ? I know that if it was a full month, it's
> > easy to do, but let's say it has an entry on 13.01.06 and an exit on
> > 20.10.06 , then it becomes more complicated. Also the calculation has
> > to be performed on several thousand cars at once...

> > Any help ??

I'm trying to calculate the total fee between two dates of a service
with a monthly fixed fee. This means that the fee per day is different
depending on the month (or even year if it's February).

Example...

Assume the monthly fixed fee is 1000.

From 05 March (27 days to count) to 20 April (20 days to count)
the calculation would be: 1000 * (27/31 +20/30) = 1537,63
From 05 April (26 days to count) to 20 May (20 days to count):
1000 * (26/30 + 20/31) = 1511,83

I know it's possible to use something like
Datedif(data1;date2;"d")/30,4375 but this uses an average number of
days per month and doesn't produce an exact enough result.

The problem is to find the exact number of months WITH decimals to
multiply the fee with... (keeping in mind also that it can be more than
12 months).

Does anyone know a simple trick to solve this?

Thx

--
effem
------------------------------------------------------------------------
effem's Profile: http://www.excelforum.com/member.php...o&userid=16112
View this thread: http://www.excelforum.com/showthread...hreadid=275586

A1:A200
01/07/08
07/07/07
04/23/07
11/30/06

C1: I need average days between dates, considering leap years.

And is it possible to format C1 to equal.."2 month 3 days"
Thanks.

I have a worksheet with invoices for the last four years. I need to average
the number of days between the order date and the shipped date. However, I
also need to have the average number of days an invoice stays open for each
quarter of the fiscal year. I have been given the following formulas to try
=AVERAGE(IF(INT(MONTH(C:C)+2/3)=4 AND
=AVERAGE(IF((YEAR('Copy Link'!$C$1:$C$196=F$32))*(INT((MONTH('Copy
Link'!$C$1:$C$196)+2)/3)=F$33),'Copy Link'!$N$1:$N$196))
but they don't compute correctly for some strange reason.
If you have any advice i would truly appreciate it. Any solution, please I
really need to know answer.
Thank you
Kathi

I need to calculate the average client visit days for our sales staff & I'm
stumped. Last year I entered each week's total visit days separately and my
spreadsheet was large. This year I thought I'd be clever and enter a monthly
total instead.

I created an abbreviated example of my spreadsheet below. I feel silly, but
I can't figure out how to get the average visit days per week based on the
total visit days per month. Can you help with the calculation formula?
Thanks!

Visit days per week: ?
Total visit days : 25
Name: John Doe
Jan 7
Feb 10
Mar 8

I am trying to help someone with a function and can not figure out what would be best to use. What I have is a start rent date and an end rent date. They want to break down how many days in each month the item was on rent so they have a column for each month. For example say an item was rented on 12/14/08 and returned on 1/12/09. It was rented for a total of 30days 18 days in December and 12 days in January. So I would need a function in each column that would return a 14 in the December Column and a 12 in the January Column. Any help would be greatly appreciated as there are over 350 rows and they are doing the process manually right now and taking several days. Thank you in advance.

Also I just noticed that the date range is currently entered as a text in the format 2008-12-14 so YYYY-MM-DD. Don't know if the text will be a problem.

Just attached an expample. In it I am trying to find a function to fill in the green cells.

I was wondering if it is possible to recognize the different numbers of
months in a day without using additional columns. Basically I want to
calculate the cost per day per month and per year. If anyone has any
information on this, please let me know. Thanks

Hi

I am using Excel 2002 and I'm trying to calculate wait times for patients per month.

Col_1 is referral date, Col_2 is assessment date, Col_3 is Apr 2009, Col_4 is May 2009, Col_5 is Jun 2009 and so on.

There will be 3 scenarios to consider for calculation:

if col_1 and col_2 are blank then columns_3 etc will be zero

if col_1 has a date and col_2 is blank then assume col_2 is the end of the month so if the calculation is in col_3 (Apr)then the calculation will be col_1 minus Apr 30, 2009 and col_2 will be col_1 minus May 31, 2009 etc.

if col_1 and col_2 have valid dates then difference of the calculation is required for the two dates per each month column. For instance if col_1 is Apr 4 and col_2 is Apr 10 then col_3 (Apr) will be 6 days and zero in all other columns. If col_1 is Apr 4 and col_2 is May 10 then col_3 (Apr) will be 26 days (Apr 4 to Apr 30) and col_4 (May) will be 10 days (May 1 to May 10) and all other months will be zero days.

Currently I have:
=IF(AND($C2="",$B2=""),0,MAX(0,MIN(J$1,$C2)-MAX(I$1,$B2)))

but it's not letting me include the third scenario. How can I get this to work? Thanks for any and all assistance.


No luck finding an answer? You could always try Google.