Excel Month Formula
Apr 22, 2009
I need some help with formula to display a value based upon a certain date. I have a spreadsheet used within a hospital that records the date of a patients death, the calendar year for the spreadsheet begins April 08 and the year is split quarterly as shown below
April08, May08, June08 = Quarter1 (Q1)
July08, Aug08, Sept08 = Quarter2 (Q2)
Oct08, Nov08, Dec08 = Quarter3 (Q3)
Jan09, Feb09, Mar09 = Quarter4 (Q4)
I want a formula to calculate the value for the "Quarter" column from the patients date of death in the "Date of Death" column eg 02/05/08 = Q1.
Can anyone help me with this?
View 9 Replies
ADVERTISEMENT
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
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
Feb 22, 2014
get the current month name in a Cell A1 and Next month name in Cell B1. what formula should I used? This will be a part of the macro that I'm creating.
The macro will be use every 11th to the last day of the month so I want the formula to be dynamic enough to work in each day the macro will be use.
Expected result using the current Date Today
A1 B1
February March
View 2 Replies
View Related
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
Feb 24, 2014
I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?
View 2 Replies
View Related
Jun 17, 2014
set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.
Rent for the month
Start Date Year 1 Year 2 Year 3 01/07/14 Explanation
01/08/13 10 20 30 10 < 1 yr = 10
01/07/13 40 50 60 50 enter 2nd yr = 50
16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80)
16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)
formula or vba using Excel 2003.
View 2 Replies
View Related
Feb 16, 2012
I am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...
January 1 2012 to January 31 2012
The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.
View 14 Replies
View Related
Feb 13, 2010
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
View 4 Replies
View Related
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, I’m 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.
I’m 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 I’m 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
Mar 5, 2007
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
=IF((C26-B26)/B26
View 9 Replies
View Related
Aug 20, 2013
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
View 1 Replies
View Related
Jan 29, 2010
I'm after a formula this time ... i've searched the board and can't find what i need.
a cell shows 2009 December
and i'd like a formula to covert this to 31st December 2009 .... i.e. for any cell i'd like to know last day of month... and month and year ..
View 9 Replies
View Related
Jan 9, 2009
I am calculating items that refer time service to days...The formula i am using now is
IF (ISBLANK (T2), TODAY (), T2) -IF (ISBLANK (I2), MAX(H2,S2), S2)
However i'm wondering what i can replace TODAY with to obtain a static date such as 12/31/08.
This formula/data is part of a macro that will be run by novice users each month end. So each month I want the measurable date to change. for example on Feb 1 I want the Macro to give me a date of 1/31/08, the following month 2/28/09.
Is there a way to correct the formula? or use a reference table?
View 9 Replies
View Related
Nov 21, 2013
My colleague copies a date to the clipboard and then pastes it back onto the same spreadsheet. The month and day reverse (01/12/2013 becomes 12/01/2013). This doesn't happen when copying 14/12/2013 (probably because it can't be interpreted two ways) nor when avoiding the clipboard with ctrl-C/ctrl-V.
My working theory is that it's some kind of autotext or formatting function. Is there a way to disable this feature/function/glitch?
View 1 Replies
View Related
May 25, 2008
I am fairly new to VBA / Excel programming. I have been trying to write a report out of excel from our company DB (SQL2005). The database is run by our frontend accounting application - so i cant mess with it at all, must only run queries.
I need to pull the last 24 months of stock sales data(by stock code or category) out of our DB into excel by counting transactions on Customer Invoices / credits. Into a table as follows..
Stock Code--Month1-Month2-Month3
ABC1----------43------33------19
ABC2-----------2------10------25
I have managed to make a script that fullfills this need but it takes about 15 minutes to run(Due to having to loop many times per item/ per month)....
I was just wondering if anyone had any tips / advice on different ways to do this..??? Ive had a quick look at Pivottables but havent gone very far in, maybe they are the answer, but this amateur does not know.
View 10 Replies
View Related
Feb 8, 2013
Excel 2003.
Trying to show the first day in a month.
B4 shows 8-Jan-13
A2 has formula =MONTH($B$4) which returns 1-Jan-00
Both cells are formatted as dd-mmm-yy. So why is A2 showing the year as 00?
View 4 Replies
View Related
Apr 17, 2013
where i'm going wrong with this?
If ThisWorkbook.Sheets("Obs Sheet").Range("C" & Counter).Value (=MONTH(TODAY())) Then
I'm not sure how to make VBA use the excel MONTH command.
View 3 Replies
View Related
Jun 25, 2013
How to use the Month function in VBA but am not getting the results I expect after reviewing other websites.
I'm running MS Excel 2007.
Simple example:
the worsheet cell, A1 has "5/14/2013" , cell format is DATE
I want to get the month as a number. I know that year(cells(1,1)) results in 2013 so in the immediate window, I try
?month(cells(1,1))
?month(range("A1"))
Both result in Run-time error '13' Type Mismatch.
What am I misunderstanding about the Month function?
View 3 Replies
View Related
Oct 8, 2008
after HOURS of trial and error. I was able to figure this out and get a formula written that would do what I needed. I'm trying to write a macro or formula that will sum the following
View 2 Replies
View Related
Jan 21, 2010
In the Total column, I would like to determine what the total would be as from the start date till the current date
Columns "C:I" has the dates and the Monthly applicable rates associated.
(in this example, they are annual dates, but it may be that rates change in between a year as well)
In the first set of details (Mr A), the start date is 01/10/2005
Since Mr A only begins 01/10/2005, the rates from 01/07/2004 - 30/06/2005 ($9) would not apply.
However the rates from 01/07/2005 - 30/06/2006 ($8) would be applicable for Mr A for the period 01/10/2005 - 30/06/2006 (ie.9 months) ....
View 13 Replies
View Related
Feb 11, 2009
i have a report that needs to be filled out with total purchases daily that keeps a running total. So each day i have a column with a new figure. Looks like this:
Date Purcahses Total
30/01/09 10 10
31/01/09 10 20
01/02/09 10 30
02/02/09 10 40
etc
On the 20th of each month, i want to create a macro that wipes out the historical data prior to 1st of that month. i.e. on Feb 20, i want to lose all the january rows.
View 2 Replies
View Related
Jun 19, 2007
I'm making a basic spreadsheet that has to calculate monthly due dates for 'reviews' based on an initial start date.
im using:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
.... i know thats probably dead simple to you guys!
which works fine.. except when it comes to times when the start date is for example 31 january, so the sequence goes:
31 jan....... 03 March.... 31 march
and my problem is i need 1 review in each calendar month, so i need to tell that 03 March to be 28 Feb instead.
View 9 Replies
View Related
Jul 6, 2007
is there a formula to list the first and the last day of each month and for every year?.
example:
if i put in cell A1: 01/09/06 ----->01/September/2006
the result should be:
A2: 30/09/06 ------> 30/September/2006
A3: 01/10/06
A4: 31/10/06
A5: 01/11/06
A6: 30/11/06
A7: 01/12/06
A8: 31/12/06
A9: 01/01/07
A10: 31/01/07
A11: 01/02/07
A12: 28/02/07
.
.
.
and so on
how to do that with a formula?
View 9 Replies
View Related
Feb 19, 2008
I have a month number in H2 (1-12). I want a formula that will give me the previous month number. So, if h2 = 1. I need my formula cell to equal 12.
View 9 Replies
View Related
Mar 12, 2008
what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?
For example (Sunday being the first day of the week):
January 5th 2008 = Week 1 of January
January 6th 2008 = Week 2 of January
February 2nd 2008 = Week 1 of February
February 3rd 2008 = Week 2 of February
WEEKNUM perfectly calculates this, but it is applicable for the whole year.
View 9 Replies
View Related
Oct 22, 2008
i need to put a date into a month +year
like this:
1/06/2008jun/08
i did this with a format date.
After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date.
MaandTotal
jun/081jun/0810
he sees that 1/6/2008 is not the same as 2/6/2008
that is why i got 2 lines of jun
but i just want one and that he counts all the junes together
View 9 Replies
View Related
Jan 19, 2009
I am trying to create a formula to work out the last working day of each month, but I can't come up with anything that is less than the 1,024 formula limit. I am using a UK calendar so the only two holidays to effect the last working day of the month will be Easter and the last Monday in May.
This is the formula I am using which takes account of Easter, the checking for the last Monday in May is simple enough in another column, but I would rather keep it in one column, just wondering whether there is a simpler way of doing this.
A1 is a specified date and then I would like to work out the last working day of the month, on a new row, for up to the next 50 years....
View 9 Replies
View Related
Jan 27, 2009
Is there any simple formula to get no. of days in a month?
View 9 Replies
View Related
Apr 10, 2007
Is there a formula in excel that will automatically populate a field with the prior months end date? For example - If my spreadsheet is opened on March 25, I would like the date in cell A1 to read 2/28/07.
View 3 Replies
View Related
