Formula For Adding 1 Month
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
ADVERTISEMENT
Nov 12, 2009
I have forumlas that will look at this cell and take action of the month in a different cell is either 1 month greater (Frontmonth+1) or less (Frontmonth-1) than "Frontmonth". As we approach December I'm realizing that logic will breadown since the FrontMonth+1 would be 13, not 1 (January)
Is there a way to get excel to add 1 month to just the month number so that if Frontmonth = 12, Frontmonth+1 would return 1, not 13?
View 9 Replies
View Related
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
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
Apr 29, 2009
I have a date on the top left hand corner of my excel sheet. I want to add exactly one month to that date in a vertical list (without using micros)
Example:
1/4/2005
1/5/2005
1/6/2005
1/7/2005
1/8/2005
.
.
etc
PS: I tried adding 30 (or a similar number like 29). It works but not if I want a large list of dates, which is what I want.
View 7 Replies
View Related
Mar 1, 2007
I have the following (just a sample) "dashboard" ....
View 9 Replies
View Related
Apr 19, 2007
I have this macro (below) which produces the the date on a file name when I update it every month. However, I would like it to show a different version of the date and in a different location.
At present it produces the following (example):
Ice_Cream_Report_Month_07-02
What I would like is:
Ice_Cream_Report_Month_Feb07 (or even Feb-07 would be fine)
Macro:
Option Explicit
Private Sub ComboBox2_Change()
End Sub
Private Sub ComboBox3_Change()
End Sub
View 9 Replies
View Related
Oct 15, 2008
I am trying to automatically add the last day of the month based off of the month
for example
a1 = beginning date
a2 = end date
a1 will always be the 1st day of a given month
then based off a1, know the last day,(i.e.... october 1, then put 10-31-08 into a2, or november 1, then put 11-30-08......)
View 10 Replies
View Related
Sep 6, 2013
CASH BK
CASHIERS
DATE
CASH
TOTAL
DIFF
SALES
GBP
AR
GBP
AR
GBP
[code]....
above is the sheet template we use on a monthly basis. I would to like to use a command button which will run a macro creating a new sheet with the sheet name each month. The idea is to have a text box and a command button,enter the month in the text box and click the button. A sheet for the month to create at end.
View 2 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
Sep 28, 2009
I have the following data:
column a: column B:
1
7
9
25
I need a formula to make column B equal to the current month adding the day in column A. so that column B equal the following:
column a: column B:
1 09/1/2009
7 09/7/2009
9 09/9/2009
25 09/25/2009
View 3 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
Dec 19, 2012
I have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.
=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.
My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)
This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")
View 8 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
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
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
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
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
Jan 26, 2010
My problem is that I want to select a cell with a "Sum" formula ex.
View 5 Replies
View Related
Apr 18, 2008
here is my formula
=SUMPRODUCT((MONTH($A$10:$A$624)=1)*($C$10:$C$624="DO"))
I want to add this:
($C$10:$C$624="DO")) AND: "SD" AND "V"
how would i add these other two factors?
View 9 Replies
View Related
Nov 21, 2008
When, Cell A1 is blank
1] Formula function : =MONTH(A1)
View 9 Replies
View Related
