First day of Next Month

Hello,

In A1 I have the date 3/10/10.  

In B1 I would like to insert a formula that will show the Date 4/1/10.

Basically, I would like a formula that will show the first of the next month 
no matter what date is shown in A1.

Thanks for the help.
0
Utf
3/17/2010 12:18:02 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
7829 Views

Similar Articles

[PageSpeed] 0

Try this:

=DATE(YEAR(A1),MONTH(A1)+1,1)
-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Jim" <Jim@discussions.microsoft.com> wrote in message 
news:3A3346E7-C4DE-4D40-A76B-14A7DA5186F8@microsoft.com...
Hello,

In A1 I have the date 3/10/10.

In B1 I would like to insert a formula that will show the Date 4/1/10.

Basically, I would like a formula that will show the first of the next month
no matter what date is shown in A1.

Thanks for the help. 


0
RagDyeR
3/17/2010 12:57:01 AM
Here's another one...

=A1+31-DAY(A1+31)+1

Format as Date

-- 
Biff
Microsoft Excel MVP


"Jim" <Jim@discussions.microsoft.com> wrote in message 
news:3A3346E7-C4DE-4D40-A76B-14A7DA5186F8@microsoft.com...
> Hello,
>
> In A1 I have the date 3/10/10.
>
> In B1 I would like to insert a formula that will show the Date 4/1/10.
>
> Basically, I would like a formula that will show the first of the next 
> month
> no matter what date is shown in A1.
>
> Thanks for the help. 


1
T
3/17/2010 1:37:41 AM
That may not work for all dates.

The easiest example is January 31, 2010.



"T. Valko" wrote:
> 
> Here's another one...
> 
> =A1+31-DAY(A1+31)+1
> 
> Format as Date
> 
> --
> Biff
> Microsoft Excel MVP
> 
> "Jim" <Jim@discussions.microsoft.com> wrote in message
> news:3A3346E7-C4DE-4D40-A76B-14A7DA5186F8@microsoft.com...
> > Hello,
> >
> > In A1 I have the date 3/10/10.
> >
> > In B1 I would like to insert a formula that will show the Date 4/1/10.
> >
> > Basically, I would like a formula that will show the first of the next
> > month
> > no matter what date is shown in A1.
> >
> > Thanks for the help.

-- 

Dave Peterson
1
Dave
3/17/2010 2:12:40 AM
On 3/16/2010 8:18 PM, Jim wrote:
> Hello,
>
> In A1 I have the date 3/10/10.
>
> In B1 I would like to insert a formula that will show the Date 4/1/10.
>
> Basically, I would like a formula that will show the first of the next month
> no matter what date is shown in A1.
>
> Thanks for the help.

One of a few ideas:

=EOMONTH(A1,0)+1

= = = = = = =
HTH  :>)
Dana DeLouis
0
Dana
3/17/2010 2:24:48 AM
Ooops!

Yeah, you're right. Don't know what I was thinking of.

Disregard that formula!

-- 
Biff
Microsoft Excel MVP


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4BA03A98.B9477464@verizonXSPAM.net...
> That may not work for all dates.
>
> The easiest example is January 31, 2010.
>
>
>
> "T. Valko" wrote:
>>
>> Here's another one...
>>
>> =A1+31-DAY(A1+31)+1
>>
>> Format as Date
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>> "Jim" <Jim@discussions.microsoft.com> wrote in message
>> news:3A3346E7-C4DE-4D40-A76B-14A7DA5186F8@microsoft.com...
>> > Hello,
>> >
>> > In A1 I have the date 3/10/10.
>> >
>> > In B1 I would like to insert a formula that will show the Date 4/1/10.
>> >
>> > Basically, I would like a formula that will show the first of the next
>> > month
>> > no matter what date is shown in A1.
>> >
>> > Thanks for the help.
>
> -- 
>
> Dave Peterson 


0
T
3/17/2010 2:55:28 AM
Reply:

Similar Artilces:

Restrict a day
On a form, I have a text box in which I want to enter a date. I want to restrict it so the date entered is a Sunday. How can I do this? I think you mean Day or you mean date? if you mean day you can try this... Click on the text box property, then on the (Validation Rule) type Sunday, then you can add any message for the users in the (Validation Text) Type "Only Sunday is acceptable" or any other message to be displayed if different value been entered. Also in the (Default Value) room on the property you can add Sunday so you don't need to type it every time. Hope it will he...

bar chart to compare month and year data
I would like to creat an excel chart that overlays totals for two years for different categories. Here is the data 2007 columnheader1 columnheader2 columnheader3 Total Jan 1 1 2 4 Feb 2 0 1 3 March 1 1 4 6 2006 columnheader1 columnheader2 columnheader3 Total Jan 2 4 2 8 Feb 2 3 ...

Formatting columns for Months, Qtrs, Yrs
I deal with a lot of historical data. I want to sum this data into months, quarters, and years. I usually use Groups to make it easier to view each so if I want to see only yearly summaries I click the highest level, if I want to see quarters I click the next level. The only problem is that when I show the quarters, the years appear too because the way they are grouped. I have tried different ways to group them but can't find anything better. Is there a better way to do this? Maybe without grouping? A way to name the colums and then have a drop down choice box or something to s...

Display billing month based on start date and end date
i have 3 textbox in a form with date type as date. 1- StartDate (bound to table) 2- EndDate (bound to table) 3- BillingMonth (unbound) i want to display billing month based on start and end date according to criteria that if startdate is 1/1/09 (format dd/mm/yy) and enddate is 28/1/09 (format dd/mm/yy) , billingmonth should display [Jan-2009] if startdate is 25/11/08 (format dd/mm/yy) and enddate is 28/1/09 (format dd/mm/yy) , billingmonth should display [Dec-2008 to Jan-2009] i have not much vb knowledge but tried the following code as control source of billingmonth and as...

Next Scheduled Release in February 2007?
At Convergence last Spring the CRM team repeated numerous times that they were on a rapid development and release schedule and that partner and customers should look forward to a release in February 2007. I don't see any documentation about what's in store for 2007 -- though I have learned more from colleagues who resell SalesForce.com about what's in store for that CRM solution. Where can I learn more so I can help better service our customers and prospects? Search on google and news.google.com for crm titan to get the current public information. As always any dates ment...

How do I set up "next record" in letter merge
I want to do two merge letters per page with the next name and address in each letter. I can't see the field codes and can't add "Next Record" as I can in Word. How can I do that in Publisher without going into Catalogue merge which is very hard to work with. I have tried copying <<next record>> from proper word format but that doesn't work. thank you Dr. Ernest D Dr. Ernest <Dr. Ernest@discussions.microsoft.com> was very recently heard to utter: > I can't see the field codes and can't add "Next Record" as I can in > Word...

30 day trials for DRS add-ins
Attn: RMS Users and Resellers We've just introduced 30-day trial downloads for most of our RMS add-ins. Download from: http://www.digitalretailer.com/rmsaddins Applications install in less than a minute and will run for 30 days. To permanently authorize an add-in, a registration key is issued with purchase. Contact your RMS reseller for more information. AUTOGEN* -- autogenerate item lookup codes; automate barcode type, sales tax by location (HQ), Supplier Reorder Number to Alias, and more POWER OPS* -- generate new items in seconds; create thousands of matrix entries in minutes; automa...

Fixed Assets-Depreciation for 18 months
Hi, Our client has been having 12 month fiscal periods (ending June). This year the F/Year will be extended to 18 months (ending Dec), and from thereon it will be 12 months. (Jan to Dec). Most of the fixed assets are setup as Straight line orig life. When we rebuild the GL calendar and FA calendar to be 18 months and recalculate assets for the year, the annual depreciation amount is equivaly divided in 18 months for monthly depreciation. ie. If an asset was having a monthly depreciation of $100, with annual amount of $1200, now it is getting 1200/18 per month=$66.67. Where as our client...

Reoccuring meeting missing the first appointment.
I have a user that has accepted several reoccurring meetings that start later this week and end at some point in the future. Outlook gives the message that the meetings are out of date, but they actually start in the future. When the user accepts the meeting all of the dates show up except for the initial meeting. If you open one of the meetings it indicates the correct date for when it started, but if you look on that date there isn't a meeting. It's a little complicated to explain so I'll give an example. Today's June 21st. User gets a reoccurring meeting that starts Ju...

Messages on Server for 90 days only
My newsgroup reader (Mozilla Thunderbird) gives me a message on all Microsoft Newsgroups for any message that is more 90 days old that the message has "expired". Is it a Microsoft policy to remove messages from the public server after that point? If so, why remove them so quickly; the shelf life of the messages should be at least several years. I can search for older newsgroup items on Google Groups, but the listing on that website is not as good, in my view, as the newsgroup itself. David I've never tried the website access, only Google Groups, but...

Money 2007
Okay, it's dumb of me to be bothered by this, but I'd really like to find the answer to this. I remember that, at some point while I was setting up Money 2007 I had a choice as to which day I wanted to be the first day of the week on the application's calendar. I selected Monday, thinking it would display the week the way Outlook 2003 does. Well, it doesn't. I'd like to set Sunday to the first day of the week, but I'm danged if I can find out how to change the setting again. Does anyone know? Thanks On Thu, 23 Nov 2006 18:55:02 -0800, jimmuh <jimmuh@discussi...

Monthly files
I have a monthly files where every day is a sheet, the beginning of which references a cell on the sheet before. Is there a way of having this done without introducing the link manualy on every sheet? Ex: A6 of Dec 10 = g24 of Dec 9, etc Thanks beforehand dimitry schaff, One way you could do this, is to set it up once and save it as a template file. The next time you open the file, to input data. When you are done, save the file as that Month.xls. The template would be MonthlyBudget.xlt (for example). If you did this in December 2009. The next time you open MonthlyBudg...

Outlook Meeting alerts - all day appointments
Our office sends all day appointments (so that they appear at the top of the calendar day) for work from home, vacation, and sick notices to our respective teams so that at-a-glance we can see who is out for the day. We ask that everyone select Alert = None. Recently we have experienced this issue: Alerts are being sent to the invitees. When the sender is told of this and they open their appointment, None is showing. (This never happened in the past. We're not sure what might have changed except that we all went to 2007 last August.) Is this a bug or is there some configur...

Month Calender / MCN_GETDAYSTATE / more attributes ?
Hi All, Im working with MonthCalendar. In the handler of MCN_GETDAYSTATE I have to provide bits in a table to set specific days to BOLD. Is there any possibility to support several attributes (e.g. BOLD,UNDERLINED,....) like in Microsoft Project Calendar to mark different things? Thanks Richard ...

First Office 2008 Update Released 2/29/08 #2
I just got the first 2008 Update and it updates the Microsoft AutoUpdate.app from 1.2 to 1.2.1 Looks like they are getting ready for the real update soon. I have no idea what was change in the Microsoft AutoUpdate.app but I suggest you get it to be ready. ...

Wrong insertion of national holy days in outlook 2000
Can anyone tell me why insertion of national holy days (Easter, christmas, nationalday etc..) inserts itself in 2003(!) when we now try to update our calenders? It is now "double" for 2003 and nothing to see for 2004.. We use norwegian outlook 2000 (and exchange 2000) Lars Hansson Inst. of transport economics, Oslo ...

Calculate 6 Months
I have a spreadsheet where I need Excel to calculate (ADD) 6 mouths automatable for anther date. The mouths need to be consecutive. IE in one cell I will put 1/29/2005 and in anther cell I need Excel to put 6/1/2005 Please explain why six months added to 1/29/2005 is 6/1/2005 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jason" <Jason@discussions.microsoft.com> wrote in message news:4682E24E-7EF3-449E-ABB6-B52B0C660B1B@microsoft.com... >I have a spreadsheet where I need Excel to calculate (ADD) 6 mouths > automatable for anther date. The...

+113.13% net return in 22 trading months.
+113.13% net return in 22 trading months. Options specialist CDM Trading has averaged +5.14% net return per month for its clients since October 2001. Details are available at: http://www.investinginoptions.com/performance_report.html ...

Counting days elapsed
Hi, I have a form that is showing the results from a query, the form shows 'reported date' and 'review date' fields, i would like to show how many days have elasped between the two dates, do i 1) Just place a text box on the form and write some expression to display this ? if so how 2) Do i redesign the original query - how 3) Do i add an additional field to the original table - then what !!!! ? Help please. Tony On Wed, 12 Mar 2008 14:12:01 -0700, blake7 wrote: > Hi, I have a form that is showing the results from a query, the form shows > 'reported date'...

How can I send a copy of monthly events on calender to others
I have just spent a lot of time putting all events on calender and now I would like to send out each month a list to others ,how can I do this please. "brianray" <brianray@discussions.microsoft.com> wrote in message news:8DD77417-25CD-4F47-9250-0ACD8C9C1E77@microsoft.com... >I have just spent a lot of time putting all events on calender and now I > would like to send out each month a list to others ,how can I do this > please. Do you need this in a format they can add to their calendars or just so they can read it? What version of Outlook? Versio...

Next Cheque Number
Hi, Was wondering if anyone knows a way that the 'Next Cheque Number' in the Chequebook Maintenance can accidently be reset? I have a customer who says that the next number has changed and they are wondering how this could have happened as it would appear that no-one has changed it through the maintenance routine? Thanks -- Jean I tried different tests and couldn't replicate. I haven't had any reports from Customers over the years. -- Mick "JB" wrote: > Hi, > > Was wondering if anyone knows a way that the 'Next Cheque Number' in the >...

How to jump to the next text box ?
Hi, I have the following scenario: I have several text boxes in each text box must be filled up to 5 characters (letters and numbers) How to jump to the next text box automatically after filling the 5 characters ?????? I try with the following code but is not working well: Private Sub txt1_Change() On Error Resume Next Me.Refresh Me.txt1.SelStart = Me.txt1 - 1 If Len(Me.txt1) >= 5 Then Me.txt2.SetFocus End If End Sub Me.txt1 <<<< First Text box Me.txt2 <<<< Second text box etc...... any help will be wellcome ! -- Message posted via http://www.access...

can i select from list by typing first letter of word?
I HAVE VERY LONG LISTS OF VEHICLES/CUSTOMERS ETC, IS THERE ANY WAY THAT I CAN KEY IN THE FIRST LETTER OF WHAT I WANT AND THE LIST MOVES DOWN TO THAT SECTION? Yes, but you don't need to yell (use of all caps is yelling). You need a Worksheet_Change event macro and a regular macro to accomplish this. You can have a cell into which you type the first letter of the section you want. Hit Enter and the screen will immediately jump to put the first cell of that section at the top left corner of the screen. The macros look like this: Private Sub Worksheet_Change(ByVal Target As Range) I...

Date to a day (Monday, Tue, etc?)
I would like to convert numeric dates to letter days of the week. For example, 9/13/2005 would convert to "Tuesday" or "Tue". 10/20/2005 would convert to "Thursday" or "Thu". 11/20/2005 would convert to Sunday" or "Sun". Is there a way to do this for all dates in the current year and future years? Format>cells>number>custom and ddd or dddd, if you want them in another cell just link to the source and use the same custom format -- Regards, Peo Sjoblom (No private emails please) "Taylor" <Taylor@discussion...

How to list dates in a month of particular weekday
MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? You didn't post a lot of detail about your setup. Assuming the month number is in A1 and your list of Fridays and Saturdays is to be listed in Column B starting at B1, put these formulas in the indicated cells... B1: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,2)) B2: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(...