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!

Many thanks!

- VBA to Determine Working Days per month/per year
- How to Set a fix # of days per month
- Distribute Days Per Month Between Date Range
- Amount of working days per month
- How do i write a formula to count the number of days in a date range, split per month
- Rolling days of month total
- Summarizing of columns for different days of month
- Translate ~Number of days in a week to number of days across months
- Adding a start and finish date to a function counting no. of specific days in month
- Summarizing of columns for different days of month
- Average rate per month
- How many items per month and average
- Percent per month
- Daily running average with overlapping months
- Averaging cells only if there is data in them
- Average Of X Months & Place Result Based On Date & Length Of Time
- Days per month for calculating storage days
- Difference between two dates in months with decimals
- Average days between multiple dates
- Average Days Open IF between dates
- Average Calculation
- Invoiced days per month
- Inputing different days in a month for a formula
- Date Calcs Per Month

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.

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

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

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

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

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.

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!

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

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

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

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!

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!

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?

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!

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

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

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

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.

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

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

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.

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

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.