Enter A Date And Return The Fiscal Month

Dec 15, 2008

Is there any way to defined our own fiscal month. I have a fiscal year where the start & end date is different from the normal calendar. I have store the start & end date on the different column. What i need is that when people enter a date, it will look up to the table and return the fiscal month.It's something like If the value is >=column A and <=column B, then the fiscal month=column C(refer to the below table). But i have no idea how to make comparison on date value. what kind of formula i should use? And also how to write the code if i want to use macro to implement this?

Start End Month
09/21/08 10/18/08 Oct
10/19/08 11/22/08 Nov
11/23/08 12/27/08 Dec
12/28/08 01/24/09 Jan
01/25/09 02/21/09 Feb
02/22/09 03/28/09 Mar
03/29/09 04/25/09 Apr
04/26/09 05/23/09 May
05/24/09 06/27/09 Jun
06/28/09 07/25/09 Jul
07/26/09 08/22/09 Aug
08/23/09 09/26/09 Sep

View 6 Replies


ADVERTISEMENT

Fiscal Week In Any Month Date

Jul 24, 2013

I have data for 6 monthsone column has all the dates

1/1/2013
1/2//2013
1/5/2013
1/8/2013
1/10/2013
1/15/2013
1/21/2013
1/31/2013

I have for 6 months like these dates

I need to convert all these dates to week 1- week 5
week 1: 1-7, week 2: 8-14, week3: 15-21, week4: 22-28, week5: 29-31
Instead of 1/1/2013 it should show week1 instead 1/10/2013 week2 like this

View 9 Replies View Related

Macro To Create Fiscal Week To Month And Date

Dec 2, 2012

I have to generate daily report in which data is fiscal week format. We need to show Fiscal week in actual month and day format. create a macro so that for every fiscal week anywhere in the sheet, it automatically converts fiscal week to month and days of the week

For example: WK 1 OCT FY2013 get converted to Oct 1-7

WK 2 OCT FY2013 coverted to Oct 8 - 14

WK 3 OCT FY2013 get converted to Oct 15 -21

WK 4 OCT FY2013 to Oct 22-28

WK 5 OCT FY2013 to Oct 29- Nov4

View 4 Replies View Related

User Defined Function To Return Fiscal Year Of A Date

May 2, 2012

I am working with a fiscal year that starts in December and ends in November. I want to make a user defined function that will return the fiscal year of a date. I've created the below code, but it returns a zero.

Function FiscalYear(DateFY)

If Month(DateFY) = 12 Then
Year (DateFY) + 1
Else
Year (DateFY)
End If
End Function

View 3 Replies View Related

Calculate Fiscal Month

Oct 7, 2008

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 Related

Excel Formula To Calculate Fiscal Month

Dec 19, 2012

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)

View 4 Replies View Related

If Date Is Between 1st Through 1th Of Month Return Value

Jul 31, 2014

If I make a sale between the 1st of each month through the 1th of each month I will be paid on the 31st of that month. If I make a sale on the 16th of each month through to the last day of each month I will be paid on the 15th of the next month. I need this formula to work for every month.

cell e has date of sale
cell h has the returned value of the formula above

View 2 Replies View Related

Return Date Of 3rd Day Of Following Month

Jun 27, 2012

I need to return the date of the third day of the month following the date in cell a1:

So, If A1= 06/01/12
A2 should = 07/03/12

View 2 Replies View Related

Return Date Of Specified Day In Month

Sep 13, 2007

Is there a end of "week" function which shows only the date the friday? says ..if today is 9/13/2007, the cell will show 9/7/2007...

View 3 Replies View Related

Return Date Of Last Day Of Given Month

Oct 16, 2007

I'm after a formula(s) which will give the start date and end date of a month using the month name. For instance, if cell A1 contained OCTOBER 07 the formula result for the start of the month would be 01/10/07 and the formula for the end of the month would be 31/10/07. I have plyed around with the EOMONTH function but this only workd when an actual date is entered.

View 8 Replies View Related

Add Fiscal Quarter To Fiscal Macro?

May 13, 2014

I have a Fiscal Date macro that will looks at a date to give me the current fiscal month or week, but I would also like to add the functionality for it to give me the quarter.

In my business, the fiscal year starts in October. The quarters of course are as follows:

Oct-Dec = Q1
Jan-Mar = Q2
Apr-Jun = Q3
Jul-Sep = Q4

I would like for the quarter to be formatted with the year ex. FY14Q1

Here is the Code, but I have also attached an Excel file with the .bas file.

[Code] .....

Attached File : Fiscal.xlsm‎

View 3 Replies View Related

Return The Month Integer From A Date

Aug 5, 2008

Upon trying to use the Month function (to return the month integer from a date), in excell 2007, I get the

"Expected variable or procedure, not module

followed by (if I drill down into the help file)

There is no variable or procedure by this name in the current scope, but there is a module by this name"

error message

Do I assume there is a way around this (or if I have to do something to 'activate' the month function)

View 9 Replies View Related

Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month

May 8, 2009

how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:

If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008

Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.

View 3 Replies View Related

Return A Date Based On Year / Month

Jan 21, 2009

formula off here i use all the time relating to finding and sumproducts for specific months and years i.e.

Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula...

is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009?

The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.

View 9 Replies View Related

Excel 2003 :: Getting Formula Which Will Return Each Date Of Month

Sep 10, 2013

I have a cell (A1) which contains the month. A2 contains the year. I need a formula which will return each date of the month.

View 7 Replies View Related

VBA Code To Return Previous Friday And Month Before Date

May 8, 2014

How to write a VBA code so that it would calculate the date of the previous Friday and from there Return the date exactly 1 month before; on any given day.

For example...
today is may 8th

So if I run the code the dates returned for last friday should be 05/02/2014
and 04/04/2014 should be returned AS the DATE exactly a month from 05/02/2014

View 6 Replies View Related

Return Month Number Of Date Unless Cell Empty

Apr 9, 2008

I am using the MONTH function in a formula. The Month value of an empty cell is returned as "1". Why is this? Is there any way to return a null value or zero?

View 6 Replies View Related

Convert Fiscal Date To Corresponding Week Number

Nov 12, 2009

I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on. The fiscal year always starts on February 1 regardless of the day of the week. The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.

I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday). =INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2. I need to make the formula "not care about" the day of the week.

View 3 Replies View Related

Get Date From Week Number And Year ( Fiscal Calendar)

Jan 22, 2013

I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.

View 6 Replies View Related

Formula To Output Upcoming Quarter End Date (for A Broken Fiscal Year)

Jan 20, 2014

I am looking to create a function that outputs the upcoming quarter end date based on a specified start date, for which the quarter end is based on a broken fiscal year ending december 15.

As an example, say that you sign up as a customer with an internet provider on 2014-01-01. The internet provider charges all their clients on a quarterly basis and have a broken fiscal year ending on december 15. Hence, as you signed up on 2014-01-01 you will be charged on 2014-03-15, which is the date of the company's first quarter end.

So what I would like to do is to set up a function that outputs the first date I will be charged based on the date that I sign up. If I sign up between december 2013-12-16 and 2014-03-15, the formula should output 2014-03-15. If I sign up between 2014-03-16 and 2014-06-15, the formula should output 2014-06-15 etc. etc.

View 1 Replies View Related

Calendar Control To Enter Chosen Month As Worksheet

Jul 17, 2009

I have a worksheet that has a Calendar on a worksheet from Calendar Control 8.0.

I want to be able to pick a month from the calendar and press a button to convert the selected month into a worksheet. I have a custom made calendar worksheet that I would like for it to be converted into.

I have uploaded my workbook. It contains the custom calendar worksheet as well as the calendar control.

The file is called "calendar.xls".

View 9 Replies View Related

Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month

Jan 28, 2010

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.

View 9 Replies View Related

Create Year To Date Sales Comparing 4 Years Month By Month?

Dec 25, 2013

Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.

View 10 Replies View Related

Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)

Mar 20, 2009

I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data

On the last row of each month, Im trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

Im having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but Im not sure how to make the first array dynamic or if this is even correct approach.

Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

View 9 Replies View Related

Dates - Show Month Only, And Actually Be The Month Only (not Just Format The Date)

Jul 28, 2008

I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.

How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?

View 9 Replies View Related

Enter Vs Return

May 18, 2007

Im using an old IBM tb3270 mainframe as described in this historic post:
[url]

ive tried {ENTER}, ~, char(13), etc etc but every one of them is just going onto a new line! Anyone any ideas how to get it to send the enter fuction, from the keypad?

View 3 Replies View Related

Prefill Text Box In Userform With Today Date But Allow User To Enter Unique Date

May 29, 2014

I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.

[Code] .....

Attached File : Staffing Report 1.90.xlsm‎

View 7 Replies View Related

Reflect Month Name When Column A Date Is Same Day And Month

Jan 8, 2014

How to correct this formula to get correct output.

=IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),
"January","February","March","April","May","June","July","August","September","October","November","December"),""))

It's working fine if the year is 2014 but not if the year is different.

Conditions:

If the date is today then only the month should display else blank. The year should be ignore.

For example:

08/01/2000: January
01/01/2000: blank cell
08/01/2014: January
01/01/2014: blank cell

The date is in column 'p' in dd mm yyyy format. In short if the date is current date that is for today '8' then only the month should be display in the output.

View 10 Replies View Related

Month(Date): If The Month Is Not January It Works

Aug 24, 2009

I have a problem calculating something that happened last month if the month is january. At the moment, if the month is not January it works:

View 3 Replies View Related

Popup Or Userform Enter Date And Delete Rows Containing That Date

Nov 26, 2013

I wondering if it's possible to create a code to have a popup where the user can enter a date in mm/dd/yyyy format and then all rows containing that date will be deleted.

View 9 Replies View Related







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