# UDF To Calculate The Month

Aug 13, 2007I am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.

ie

05/01/07 - 06/02/07 = Jan

06/02/07 - 05/03/07 = Feb

I am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.

ie

05/01/07 - 06/02/07 = Jan

06/02/07 - 05/03/07 = Feb

ADVERTISEMENT

I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09

1 Jan 09

1 Jan 09

1 Jan 09

2 Jan 09

2 Jan 09

3 Jan 09

3 Jan 09

3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09

Feb-09

Mar-09

Apr-09........

I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.

View 4 Replies View RelatedI am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.

Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?

I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.

Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?

IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".

The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).

Sample data:

02-06-09 to 18-06-09 earned $2500

19-06-09 to 23-08-09 earned $3500

24-08-09 to 31-12-09 earned $4500

I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.

I have been using Excel for a while but i'm kinda stuck at one point where i have to calculate the sum of the numbers corresponding to the month of the data.

Column 1 has all the dates in different months - in this workbook i've included only jan, feb and mar. The second column has the counts corresponding to the dates.

Now if a user inputs the month (1,2,3) number in F2, then we need to compute the sum of all the numbers from second column corresponding to the month in column 1 and display in cell F4.

I need a formula to calculate a date 6 months forward from the start date. However, in the calculated 6th month, I need it to the day before the start date.

ie: start date: 26/12/13 -- End date: 25/06/14

I have a workbook where I would like to enter the date of the starting month when the workbook is being used, and I want it to calculate in the next cell across the ending of that month.

View 4 Replies View RelatedI am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?

View 3 Replies View RelatedI am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.

For example:

I have a workbook with two worksheets in it.

The first worksheet is the input page.

On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.

Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12

Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP)

(in this example, this should be 3) and displayed as 03-Sep-12

Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1

The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.

The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.

Daily

Date

USD

29 June 2012

1.0191

1 August 2012

1.0507

[Code] ........

Worksheet 1, Cell C1 should display the value of 1.0266

For any given year, I would like to calculate the date of the third Wednesday of each month for that year, plus the interval (in weeks) between two consecutive months (Which will be either 4 or 5).

Example:

Enter 2013 in cell A1

Output would be:

A2 - Jan 16

A3 - Feb 20

A4 - Mar 20

A5 - Apr 17

..

A13 - Dec 18

AND

B3 - 5 weeks (interval Jan-Feb)

B4 - 4 weeks (interval Feb-Mar)

B5 - 4 weeks (interval Mar-Apr)

..

B13 - 4 weeks (interval Nov-Dec)

Ive created an invoice. The data is being entered on the previous sheet & then this invoice reads the data from the previous sheet through the formulas as shown below. The data that is entered, is entered contineusly everyday, so the invoice calculates the amounts from the entire data input sheet. I need to add some controll to the invoice where I can select a starting date & an ending date, and then the amounts must be calculated on the invoice according to the selected dates.

H I J

27 Qauntity: Cost: Amount:

28 55 R 185.00 R 10,175

29

30 Net amount: R 10,175

31 Vat: R 1,424.50

32 Amount Due: R 11,599.50

Invoice

[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)

H28 =SUM(Divenic!G5:G5004)

J28 =SUM(H28*I28)

J30 =J28

J31 =J30*0.14

J32 =J30+J31

[Table-It] version 09 by Erik Van Geit

So yesterday I created a thread [URL] ..... that would clear a specific content if a date (or rather a day) matched the criteria.

Today I'm continuing with that document and need to sum the columns if the date in a row is the correct month and year.

Ex. Row 1 contains the date "yyyy-mm-dd" and row 2-5 contains empty cells or the value x. The x values are all random placed.

So, I want to sum all the "x" for February 2014.

I'd rather use a formula here than a macro/VBA-code but anything will do. I have tried myself with =sumifs and =sumproduct but with no success.

See attached file for example and for my =sumproduct formula.

Excelforumexample1.xlsx

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 RelatedI am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank

=IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)

The last day of the month function is shown below

=DATE(YEAR(A13),MONTH(A13)+1,0)

This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.

I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.

There is of course several ways to do this, but is there a simple and easy formula one can use.

i have a dataset which is like a timeseries with 3 columns

first is year second month third values

so lets say like this

year month day value

2004 12 29 100

2004 12 30 200

2004 12 31 300

2005 01 01 50

2005 01 02 60

.....

I need to calculate the quartiles with the condition of the year or the month or even both... I suppose i need something like an array but i couldnt make it work untill now.

I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?

I enter in Cell 1: 1/31/09

Excel calculates in Cell 2: 12/28/08

I enter in Cell 1: 2/28/09

Excel calculates in Cell 2: 1/31/09

I have a large data sheet with dates in column B - in column A, I'm trying to write a formula that will determine what fiscal month it should be mapped to.

I have the calendar listed on a different tab.

Fiscal Month

Start Date

End Date

January

1/1/2012

1/31/2012

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

The formula I tried using is: =LOOKUP(B2,Calendar!$B$2:$C$25)

I am trying to calculate the Date of the next occurring 3rd Friday of the month in relation to a Static Date in a cell. So if Static Date in cell is Tuesday of the second week of a month, then I am trying to return the date of the 3rd Friday of that month. If the static date in the cell is the third friday of the month, I just want to return that particular date. And if the static date is after the 3rd Friday of the month, I am trying to return the 3rd friday of the following month. So far here is the formula I am using:

Code:

=DATE(YEAR(A2),MONTH(A2),14+CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),4)),2,1,7,6,5,4,3))

The problem I have is that if the static date is after the third friday of the month, this continues to return the 3rd friday of the month of the static date and does not advance to the third friday of the following month. Looks like I cannot attached a sample spreadsheet but here is a screenshot of the sample sheet:

[URL]

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.

I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box.

I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13.

where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work.

I would like a formula that will calculate these previous month end dates correctly.

See example below ....

i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).

View 2 Replies View RelatedI would like to calculate the week number of the month based on a date.

Now my days would only include working weeks (Monday - Friday).

Supposed the date is 12/31/2012:

M

31-Dec

T

1-Jan

W

2-Jan

TH

3-Jan

F

4-Jan

Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:

M

28-Jan

T

29-Jan

W

30-Jan

TH

31-Jan

F

1-Feb

It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:

M

29-Apr

T

30-Apr

W

1-May

TH

2-May

F

3-May

It will be considered as Week 1 of May since it occupies only 2 days of the working week.

Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.

I have attached a spreadsheet which shows my desired result which often works.

Range C2:C13 is my desired result...

The problem /criteria is...

For every 140 meters sold the sales person receives £140 commission.

Meters (range B2:B13) can be carried to future months.

I am looking for a formula for range C2:C13 which calulates if commission should be paid in this month or not and if so, how much.

I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything.

So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.

Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name "LARSheet1 to 1" ]

I'm trying to build "Leave, Attendance Management" system using Excel. I am about 40% there so far.

The basic structure of the workbook is as follows:

12x main monthly "Leave Attendance Record" worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ].

1 x worksheet which holds "data validation" list andvlook up info. ["FormulaListSheet"]

1 x worksheet acting as a database of staff details andpay numbers. ["StaffdBaseSheet"]

1 x summary page which shows staff reaming leaveallocation. ["SummaryLeaveSheet"]

What I'm having problems with at the moment is as follows:

A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].

Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).

I would like it to work like this:

When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.

I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.

Sample of sheet setup : screenshot_LARSheet1.PNG

I have a spreadsheet and every row has an incident date. What I would like to do is calculate which relative month it is compared with today's date. I know how to show if it is in the current month, but what I want is something like the following:

Today's date is 9 July 13

Row 1 date = 9 June 13, hence calculation = -1

Row 2 date = 17 May 13, hence calculation = -2

Row 3 date = 29 Jan 13, hence calculation = -6

etc

Some sort of month to month comparison resulting in an integer.

Copyrights 2005-15 www.BigResource.com, All rights reserved