Help required on working with date

Hi 

I have dates in a column in dd-mmm-yy format, like

29-Mar-04
15-Apr-04
13-May-04

I want to put in the column adjecent to this date column
the date starting with 1st day of each month like -

01-Mar-04
01-Apr-04
01-May-04

How can this be done by use of formula?

I tried but do not get any clue to go futher.

-Shanks
0
anonymous (74722)
9/3/2004 1:41:37 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
388 Views

Similar Articles

[PageSpeed] 58

Hi
try
=DATE(YEAR(A1),MONTH(A1),1)

-- 
Regards
Frank Kabel
Frankfurt, Germany


Shanks wrote:
> Hi
> 
> I have dates in a column in dd-mmm-yy format, like
> 
> 29-Mar-04
> 15-Apr-04
> 13-May-04
> 
> I want to put in the column adjecent to this date column
> the date starting with 1st day of each month like -
> 
> 01-Mar-04
> 01-Apr-04
> 01-May-04
> 
> How can this be done by use of formula?
> 
> I tried but do not get any clue to go futher.
> 
> -Shanks
0
frank.kabel (11126)
9/3/2004 1:58:34 PM
If column A has your dates then column B should be
=eomonth(a1,-1)+1

This will calculate the last day of the previous month and then add 1
day.


---
Message posted from http://www.ExcelForum.com/

0
9/3/2004 2:09:27 PM
Hi,
use the following =EOMONTH("Date",-1)+1, where "date" 
represents the cell address of the cells you want 
evaluated.  Note that you will need to have the analysis 
toolpack addin installed to use this formula.

HTH.

>-----Original Message-----
>Hi 
>
>I have dates in a column in dd-mmm-yy format, like
>
>29-Mar-04
>15-Apr-04
>13-May-04
>
>I want to put in the column adjecent to this date column
>the date starting with 1st day of each month like -
>
>01-Mar-04
>01-Apr-04
>01-May-04
>
>How can this be done by use of formula?
>
>I tried but do not get any clue to go futher.
>
>-Shanks
>.
>
0
mmzehr (177)
9/3/2004 2:10:22 PM
Tks Frank
It worked well !!

Shanks

>-----Original Message-----
>Hi
>try
>=DATE(YEAR(A1),MONTH(A1),1)
>
>-- 
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>
>Shanks wrote:
>> Hi
>> 
>> I have dates in a column in dd-mmm-yy format, like
>> 
>> 29-Mar-04
>> 15-Apr-04
>> 13-May-04
>> 
>> I want to put in the column adjecent to this date 
column
>> the date starting with 1st day of each month like -
>> 
>> 01-Mar-04
>> 01-Apr-04
>> 01-May-04
>> 
>> How can this be done by use of formula?
>> 
>> I tried but do not get any clue to go futher.
>> 
>> -Shanks
>.
>
0
anonymous (74722)
9/3/2004 2:35:44 PM
On Fri, 3 Sep 2004 06:41:37 -0700, "Shanks"
<anonymous@discussions.microsoft.com> wrote:

>Hi 
>
>I have dates in a column in dd-mmm-yy format, like
>
>29-Mar-04
>15-Apr-04
>13-May-04
>
>I want to put in the column adjecent to this date column
>the date starting with 1st day of each month like -
>
>01-Mar-04
>01-Apr-04
>01-May-04
>
>How can this be done by use of formula?
>
>I tried but do not get any clue to go futher.
>
>-Shanks


=A1+1-DAY(A1)

will give you the first day of the month of the date in A1


--ron
0
ronrosenfeld (3122)
9/3/2004 4:11:40 PM
Reply:

Similar Artilces:

My application "has stopped working" !!!
My application, which has run fine under XP for years, is now choking under Vista, with the dreaded "Application has stopped working, Windows is checking for a solution to the problem" message. But of course not every time. That would be too easy. When Vista gives that error, is there any way to get the underlying technical details as to what "stopped working"? Does that error cover a bunch of old errors like DLL mismatches, or is it some specific new Vista thing? Thanks, Chris If you can run a debug version you should see the ASSERT messages. Tom "Chris...

Help question answer please up grading from 2000-2003
I am updating from office outlook 2000 to 2003. I am not comp. savy but trying to do a safe update and need some advice. I backed up all office files, contacts e-mails etc to a PST. When I installed 2003 disk it asked me to remove 2000 first. So I asume the procedure I would do is to add/remove 2000 install 2003 and import back the PST. sounds easy I thought since this was a update that it did everything Automactily Should my 2000 PST back up restore to the 2003 office when installed ? If I remove 2000 I have no disks to reinstall if things should go south thats why I am ...

countif links to another workbook not working
The links update fine if the other workbook is open, and all the other links update just fine even though the other workbook isn't open So the only links that don't work with the other workbook closed is the countif formula Any ideas? Sorry don't know why but I experience exactely the same problem wit Sumifs in link -- Message posted from http://www.ExcelForum.com Maybe you could rewrite your formula using =sumproduct() (which will work with closed workbooks): =SUMPRODUCT(--('C:\My Documents\excel\[book1.xls]Sheet1'!$A$1:$A$30=D8), 'C:\My Documents\e...

CRM 3.0 Reports Link Not working
I installed CRM on one server and the Database on another. Everything seems to work fine except for the Reports Link listed in the workplace. This is the error I get: ERROR An error has occured. For more information, contact your system administrator. I wish it was that simple, but I am the administrator. In the event log on the CRM Server I see nothing. On the SQL box the only strange thing I see is a 18456 Event for the user NT Authority\Anonymous Logon coming from the CRM servers IP. HELP!!!! A well known issue. Here is a hint from one of the older postings here: Have you loo...

Pivot Chart/Report Help
I need some serious help here. I've been trying to figure this one out for 3 days now and I'm getting nowhere. I've searched google extensively for anyone else that might have been having this issue but come up with nothing. OK, I have a very simple pie chart created in Access 2003. It is basically just a form in which the default view is set to "Pivot Chart." If I view this on the screen, it looks fine. If I print out the form, the chart is upside down and backwards. If I print out the report in which the form is a sub-report, the chart is upside down and backward...

SGW WORKS
SGW 2- Start Getting Wealthy On $2.00 or less Hello, Welcome to the SGW {Start Getting Wealthy} program. Before you go any further this is not a get rich quick scheme and should not be used as a primary source of income. The SGW program in simplest terms is an online lottery game and is completely dependent on chance and how many individuals will contribute. SGW originated between a few college friends researching ways to make a little extra cash entirely based on chance and contributors on the Internet. They succeeded and would like to give you the chance to also succeed. Starting with on...

Help in displaying in a cell
I had type in a number of words (< 400 word count based of microsoft works) in a excel spreadsheet, but the cell only display ############ wrap text option is checked, What's the exact problem? Using excel 2007 Is the cell formatted as Text? Apply a General format to it instead. Also, you can insert manual line breaks as you enter the text using Alt-Enter. Hope this helps. Pete On Jul 3, 9:59=A0am, "crapit" <biggerc...@yahoo.com> wrote: > I had type in a number of words (< 400 word count based of microsoft works= ) > in a excel spreadsheet, but the c...

Please help me with my Excel
I have got a new lap top and I have a good antivirus and spyware. my pc works good but once I open Excel my CPU jumps up to %100 and the strange thing is that when I click on other programs my CPU comes back to %4. The more strange thing is that when for example I press F2 to edit a cell , CPU works good but when I press Enter, again it goes back to %100. Here our IT man reinstalled my MS-Office and he did some other exotic operations but there was not any slightest change and the problem is still existing and going on my nerve. Please scientists help me ! I work with Excel 7 hrs per day ...

Subquery help PLEASE! :P
Hi all. I'm about to throw in the towel on this subquery. I've checked it and rechecked it. OK, as an overview, I have two tables, one of names of industries (automotive, energy, etc) named tblIndustries. I have another table of stock market company symbols, called tblSymbolsMain. This table has an industry type as a field for each stock symbol. I've written a function (that works great) called MultiDayPerfClose that returns a performance in terms of percentage of a stock. What I'd like to do is get an average of performance returns for each industry. I'm usin...

Help: Transferring sent items to IMAP 'sent' folder
Hi, Someone somewhere must know an easy fix for this. The University in which I work is looking to change it's default e-mail program for staff from OE6 to either Outlook XP or 2003. The problem we're trying to find a solution to is the transferral of sent messages to an IMAP 'sent' folder. There is a Microsoft KB article ( 198852) that instructs how to set up a rule using the 'Rule Wizard' which will perform the task. The article says it only applies to OL '98 but we've tested it and it works for OL XP. What we need to know now is how to automate the appli...

GP BOM should be able to select variable BOM from date or range
I had a request from prospect to see if GP could allow their Mfg Mgrs to create a cutoff or date range from which to select different BOMs. For example: They wish to change ingredients used in a dietary supplement starting Tuesday. Work orders from Tues onward would use an alternate BOM. All work orders prior to Tuesday would used the original BOM. There are other similar scenarios. Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the...

Block SMTP inbound/outbound on the border router. Any reason to let SMTP work internally ?
I found out that SMTP is open in my workstation subnets in my organization. In addtion to block the SMTP inbound/outbound traffic on the border router, I would like to confirm if there is any reason to let SMTP open internally. (we have Exch2000SP3, Outlook2002/2003 and that doesn't rely on SMTP). Not unless you have some application that uses SMTP to send mail. One example might be a monitoring package that sends alerts. In cases like that, I'd make sure that you have port 25 restricted to only a specific set of computers. -- Ben Winzenz Exchange MVP "Marlon Brown&qu...

xmerge for exchange 2003 not working
Hello all, Salam to some of you I can only copy individual mailboxes using xmerge but not all mai boxes on the server? I get an erro Any help or suggestions? Many thank I guess our answer would depend on the errror. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "dali" <dali_osmane@hotmail-dot-com.no-spam.invalid> wrote in message news:QtednRQ24-Ykd6TeRVn_vA@giganews.com... > Hello all, Salam to some of you, > > > I can only copy individual mailboxes using xmerge but not all mail > boxes on the server? I get an er...

bookmarks not working -- some do, some don't
I'm a pretty experienced FrontPage user. I'm using 2003. I know it's no longer being supported; no time to learn something new. All that said, I'm working on a site that requires bookmarks, linking thumbnails to sections below (on the same page). I've done this hundreds of times without a problem. Now, for some reason, the bookmarks won't work on this particular page. I've got one working -- but the other thumbnails (which link to other cells) won't work. YET -- the "Return to top of page" graphic and bookmark I've created, wh...

HELP PLEASE #6
i HAVE JUST SET UP OUTLOOK... I got a memo today, a pop up that said that Outlook can get rid of your spam.I thought it was from Microsoft. Now I have another tool bar on my internet and I hate it. Worse than that, I keep getting pop ups. How do I remove it.It is called SpamBlockerUtility. Relamb111@aol.com thanks look for spamblockerutilityblockerutility "rhory" <anonymous@discussions.microsoft.com> wrote in message news:4a0d01c47396$e1bdd6a0$a301280a@phx.gbl... > i HAVE JUST SET UP OUTLOOK... I got a memo today, a pop > up that said that Outlook can get rid o...

Unable to open html attachment in outlook 2000 (Please help)
Currently using outlook 2000. Got an attachment html link. Click open, a new windows --> system32 opened. Click onto the attachment again, Click save as, into my computer, double click the link, then it able to open in IE. Q: Why can't I open it directly, instead I have to save the attachment before open the html file. Please help. Thanks & Regards James something is messed up if it's opening a windows explorer window. I think you'll need to reregister IE. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for ...

OWA on Exchange 2007 not working
I have installed Exchange 2007 EE and moved my mailbox. I've also created the OWA Virtural Directory via Power Shell. To access my mailbox via OWA I keyed" http://servername/owa/userid I've received the logon screen for OWA and entered my userid and password. When I click the Logon button I get the following message: Page not found HTTP Error 404 - File or directory not found. Internet Information Services (IIS) Does anyone know why or have I may have missed trying to configure OWA in Exchange 2007 EE? I've created OWA under the "Default Web Site". Wh...

player don't work after update windows service pack!
Is here anybody who speak german??? On Sun, 21 Feb 2010 11:28:01 -0800, Suse <Suse@discussions.microsoft.com> wrote: >Is here anybody who speak german??? This is not a language discussion forum, so I assume you are after windows media information in German instead of English ? See this newsgroup for windows media player issues in German : http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.de.windows.mediaplayer (or http://tinyurl.com/yk63kgj) Or, the German "microsoft answers" site for music and sound : http://social.ans...

Unable to display folder error message--HELP
Since having Windows XP reinstalled last week, I have a new problem with Outlook 2002. I've used Outlook for several months for contacts and calendar, which I hotsync with a Palm. I do not use Outlook for email, as I use Netscape. I am not on a network. This is the problem. Both the Calendar and Contacts work fine for awhile and then I get the following message when I either click on Calendar or Contacts: C:\Documents & Settings\username\Local Setting\Application Data\Microsoft\Outlook\Outlook.pst is already in use by the maximum number of applications. Close some of these app...

Date calculations
I have a pre-given date and want to know the resulting date after adding a certain number of days in pre-given date. Mathematically it is like: pre-given date + days = resulting date Pregiven date is in one cell of a table on presentation and days and resultings date are in their own cells each. Thanx in anticipation EXCEL 2007 Cell A1:- 01/04/2010 Cell B1:- 10 Cell C1:- =(A1+B1) This gives the answer 11/04/2010 in cell C1. If my comments have helped please hit Yes. Thanks. "kharal" wrote: > I have a pre-given ...

Thumbnails: Entourage and MS Word help screens
I use OS 10.3 and Office 2004 for Mac 11.2.1 (recently downloaded the upgrade). For several weeks, Entourage messages and Microsoft Word help screens have been previewing and printing only as thumbnails. I know that Preview tends to reduce the size of documents so they will show in the small viewing window, but on my machine Entourage messages and Microsoft Word help screens preview in microscopic text--and print that way, too. The text is about 2 pts. This happens when I use any of the three printers in our office. I've noticed that while the preview PDFs are up on screen the "actu...

VLookup problem...help pls...urgent
Hi, I have wriiten the following code. Private Sub btnPastCommentsActivityList1_Click() Dim dt As Date dt = txtOldDateOfEntryMonth1.Value + "-" + txtOldDateOfEntryDay1.Valu + "-" + txtOldDateOfEntryYear1.Value 'dt = Format(dt, "dd/mm/yyyy") MsgBox dt ActiveWorkbook.Sheets("Activity_Database").Activate Range("B6").Select txtOldComments.Value Application.VLookup(cmbInitiativeDescription.Value, Range("C6:Z9999") 18, False) And Application.VLookup(dt, Range("B6:Z9999"), 18, False) End Sub I have got three columns. On...

Need help on mail merge with 4 up postcards Avery 3380
I just upgraded from Office 2000/Win XP to Office 2007/Win Vista 64 Ultimate. I had previously created postcards using Publisher and a mailing list (datsource) using Word and everything was fine in Publisher 2000. The Avery postcard stock is titled 3380 (or use template 8387) and is 4 up using landscape print. I have a deskjet 6840 that I can manually duplex on. When I try to do this on Publisher 2007, I select File-Print Merge and Multiple pages per sheet. It seems to know it's 4 up, that is on one side of the paper there are 4 printable areas, but it prints on one side: <front>...

Help: AfxGetApp()->OpenDocumentFile crashes in release mode
Help!! The line AfxGetApp()->OpenDocumentFile(...) works fine on my computer (where I compiled the application using VC 2003 .NET), but crashes on different computer that doesn't have the VC installed. I copied 3 dll's to other computer system32 dir: MFC71.dll Msvcr71.dll mfc71enu.dll The debug version works fine on both computers, but I had to copy 2 more dlls (ending with d) to other computer. I also tried linking MFC statically, but it still crashes... What am I missing here? Any help is greatly appreciated ! I just narrowed it down a little. It turns out that doc is get...

Working with DirectoryInfo
I'm working on a function that removes files related to a virus. The files are called x[1], x[2], x[3], etc, and are found in "Local Settings\Temporary Internet Files\Content.IE5\<CacheFolder>". The script fails to detect and remove the files because the variable $CacheFolder (seems to return a string instead of a folder object (although the type is reported as DirectoryInfo). The Get-ChildItem cmdlet is trying to the cache folders in my home directory instead of the correct paths. How can I adjust this function to correctly search the cache folders? function ...