Re-set a date to the first of the month

Hello,

I need to re-set whatever date is entered in a cell to the first of
that month.  For example if someone enters 9/11/2008, I need it to be
re-set to 9/1/2008.
Is this possible, and if so, can you please tell me how to do it??

thanks in advance!
0
osfard (6)
10/10/2008 11:34:46 PM
excel 39879 articles. 2 followers. Follow

2 Replies
510 Views

Similar Articles

[PageSpeed] 52

You could use a formula in another cell:
=date(year(a1),month(a1),1)
or
=a1-day(a1)+1

Then copy|paste special|values over the original range (a column???)

OS wrote:
> 
> Hello,
> 
> I need to re-set whatever date is entered in a cell to the first of
> that month.  For example if someone enters 9/11/2008, I need it to be
> re-set to 9/1/2008.
> Is this possible, and if so, can you please tell me how to do it??
> 
> thanks in advance!

-- 

Dave Peterson
0
petersod (12004)
10/10/2008 11:40:36 PM
On Oct 10, 4:40=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> You could use a formula in another cell:
> =3Ddate(year(a1),month(a1),1)
> or
> =3Da1-day(a1)+1
>
> Then copy|paste special|values over the original range (a column???)
>
> OS wrote:
>
> > Hello,
>
> > I need to re-set whatever date is entered in a cell to the first of
> > that month. =A0For example if someone enters 9/11/2008, I need it to be
> > re-set to 9/1/2008.
> > Is this possible, and if so, can you please tell me how to do it??
>
> > thanks in advance!
>
> --
>
> Dave Peterson

Hi Dave,

Both ways worked!!  Thank you!
0
osfard (6)
10/10/2008 11:55:56 PM
Reply:

Similar Artilces:

Money Changes Transaction Dates
Hi all, How can I stop Money2004 from changing my transaction dates that I have already entered into the register? This happens when I accept/match downloaded transactions from my bank. Thanks! Well, I think I just answered my own question. I found a check box to untick under online options to fix this, but I will post back if necessary. "rustyfender04" <rustyfender1@hotmail.com> wrote in message news:eY3zaLITHHA.2124@TK2MSFTNGP06.phx.gbl... > Hi all, > > How can I stop Money2004 from changing my transaction dates that I have > already entered into t...

adding months to an inputted date
I need a function that will take a date that a user has typed in a different cell and will then add two months to the date. For instance, if I type "2/12/05" in B1, then I want C2 to be: "4/12/05". Thank you for any help that you may be able to give. Logan =DATE(YEAR(B1),MONTH(B1)+2,DAY(B1)) however what do you want the date to be in C2 if B1 is 01/30/05? Regards, Peo Sjoblom "BLW" wrote: > I need a function that will take a date that a user has typed in a different > cell and will then add two months to the date. For instance, if I type >...

For Russ Paul-Jones of Microsoft re the News Headlines
Russ, it has been several days since your post re Microsoft working on the problem regarding the news headlines on MS Money not opening, and they still don't open. Just wanted to let you know... Thanks. Getting back after the long weekend, it looks like it is working now, at least in Money 2006. Still having trouble? -- Russ Paul-Jones russpj@microsoft.com MS Money Product Unit Manager "Chuck" <chleven@msn.com> wrote in message news:OLEfXCJ8FHA.4084@TK2MSFTNGP10.phx.gbl... > Russ, it has been several days since your post re Microsoft working on the > pro...

re:Restart remote computer
Try "%windir%\system32\shutdown.exe -r -t 00" (without the Quotes)i"ve never tried it on a remote> but it triggers an immediate reboot on my box... In news:27t1861l837f5cpt7ekpnmj9ogh7bea3dv@4ax.com trashcan@xrex.us wrote: > > Try "%windir%\system32\shutdown.exe -r -t 00" (without the Quotes)i"ve > never tried it on a remote> but it triggers an immediate reboot on my > box... To reboot a remote machine you need to supply the remote hosts's name: shutdown -r -m \\hostname But then you run into user permissions on both th...

Convert date to months
I have a database that has multiple dates in it. What I am trying to do is in one field I have a date and I am want that date to convert to months in another field. Can anyone help me on this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1 are you trying to find months between dates? or convert 4 to April? Look at format function for ways to display months, datediff function for months difference.. "bohon79 via AccessMonster.com" <u35329@uwe> wrote in message news:74ac2f3f40ce9@uwe... >I have a database ...

RE: Use that security update
--dedtgpho Content-Type: multipart/related; boundary="yzkfzsfqtv"; type="multipart/alternative" --yzkfzsfqtv Content-Type: multipart/alternative; boundary="qeviqcxczwaquy" --qeviqcxczwaquy Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to hel...

The Settings button keeps disappearing
Hi, I'm having a weird problem when I access CRM using the web client. When I access the site, I see all the buttons (Workplace, Sales, etc) except the Settings button. If I reboot, the button is back. My security role is system Admin. Any idea what is wrong? Thanks! Simon Are you running the Microsoft CRM Outlook Desktop client in parallel? Frank Lee, Microsoft CRM MVP Workopia, Inc. http://microsoft-crm.spaces.live.com "Simon Renshaw" wrote: > Hi, > > I'm having a weird problem when I access CRM using the web client. When I > access the site, I s...

Setting .doc default to .doc, not .dox
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel How do i do this for all new documents? > How do i do this for all new documents? I meant .doc instead of .docx Word> Preferences> Save, choose Word 97-2004 Document (.doc) from the Save Word files as: list - just keep in mind that you'll be constantly working in Compatibility Mode which will prevent the use of some of 2008's features. HTH |:>) Bob Jones [MVP] Office:Mac On 4/23/08 7:50 PM, in article ee99690.-1@webcrossing.caR9absDaxw, "dbdbdb@officeformac.com" <dbdbdb@officeform...

Reports and making it look prettier: Last Name, First Name Rank
I'm trying to pretty up my report by eliminating the forced space created by having one field of the report for 'LastName', one for 'FirstName', and one for 'Rank.' The Rank isn't too huge of an issue, and if three items in one field gets to be too much, I have no problem leaving that as a side item of sorts. But, I want my report to look a bit better by putting the names together! I want the report to go to my table, pull the LastName from that column, and pair it with the matching FirstName in the column to the right. (Since it's just...

Re: Function to copy data from a variable range?
I have a sheet with about 20,000 lines of data. From that sheet I am looking to group the data based on a parameter, into multiple different sheets. ie: If the category is "1" copy all that lines data into sheet A, If the category is "2", copy all the lines into sheet B. The problem is, the number of lines for each acatgeory type is not always the same. Some categories may only have 1 or two lines, other may have 200-300. Is there any type of function for this? Thanks! Doable but why not just use data>filter>autofilter -- Don Guillett Microsoft MVP Excel SalesA...

Setting directory for file save in macro
Hi Thanks to a generous poster I now have a great bit of code to use in a macro for Excel which saves a text file (I'm no VBA programmer so this was really helpful), i.e.: Open "Test.txt" For Output As #1 (rest of code) My problem now is this: The macro saves test.txt to Excel's current active folder, rather than the folder that the current workbook is contained in. For example, if I last saved an Excel workbook to c:\workbooks\, and the workbook that I have open exists in c:\workbooks\workbook1\, when I run the macro it saves the text file in c:\workbooks\. If I ...

Public Folders: How to set up Auto-Reply in EX2003?
Hi guys, I've configured a public folder to receive mail from the Internet, and also set up an auto-reply rule using the Folder Assistant to reply when an e-mail is sent to the folder. The folder will auto-reply just fine if I e-mail it from within the Exchange organization, but if folks from the Internet e-mail the folder, it doesn't reply. Any ideas? Thanks! Jeff I ended up figuring out what the problem was. Just in case anyone else has this problem, what I needed to do to fix it was: - Open Exchange System Manager > Global Settings > Internet Message Formats - Right-c...

HELP
I just wrote a check to the IRS, and in the Expense Catagory I indicated "Taxes : Federal Income Tax". Yet in the Income/Expense report, this payment is not included in my total federal taxes paid for this month. I should note that the check was written from an Investment Account. If I write the check from my checking account, the amount correctly shows up in the Income/Expense report. Any ideas as to why it works differently, depending on which account I pay the tax from? In microsoft.public.money, Tom wrote: >I just wrote a check to the IRS, and in the Expense Cat...

Trying to set up account, geting nowhere FAST!
From: "Lenora" <lmandre@windstream.net> Subject: Understanding what Microsoft Communities Date: Friday, September 14, 2007 5:41 PM Hello out there! What is Microsoft Communities all about? Is it something that comes with no charge when you get Microsoft Vista? This is the 4th computer I've had (besides ones at work). First a Compaq that I loaded everything I could from work, the computer guy gave me stuff...did work at home, brought it back to work, and visa versa. Second was a re-built computer that was given to me by a man in the town we had moved to...out of the g...

Printing
Hope you folks can help me out with a strange one. I have several worksheets formatted in exactly the same way as follows: Col A - width 4 Col B - hidden Col C - width 4 Col D - Width 108 Col E - Width 3 Col F - Width 11 Col G - Hidden Col H - Width 11 & Empty My print range should be Cols A:G (I have used page setup to set the scaling to fit 1 page wide by [blank] pages tall, thus each sheet will print as many pages as required depending on number of rows] When I have the print range set to A:G only columns A:E show on the print preview (and also on the actual print out) and when I m...

Calendar/Dates Help
Hi All, I have an excel spreadsheet that lists every date in the year, with a particular code in the next cell. IE: Monday 3/01/2005 11M 22M 32M Tuesday 4/01/2005 11T 22T 32T Wednes 5/01/2005 11W 22W 32W Thursday 6/01/2005 11H 22H 32H Friday 7/01/2005 11F 22F 32F Saturday 8/01/2005 11S 22S 32S Sunday 9/01/2005 11N 22N 32N Monday 10/01/2005 11M 21M 33M Tuesday 11/01/2005 11T 21T 33T Wednes 12/01/2005 11W 21W 33W Thursday 13/01/2005 11H 21H 33H Friday 14/01/2005 11F 21F 33F What I need is to be able to search by the code eg "33T" and have all the dates listed for ...

Date comparison better method
Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and '2010-06-17 23:59:59.997' Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' AND DateColumn < '2010-06-17 23:59:59.997' I am seeing in a project both the above methods of data range filering is happening in different SPs. I am trying to understand which is the better method of comparing two date values and why? [Btw i know BETWEEN considers both the upper and lower limit] Regards Pradeep I would say the following is the better approach: ...

Extracting Time from a cell that has both the date and the time
Hi Folks, I could do with some help here please. I am trying to extract the time only from a cell that has both the date and the time. Can anyone suggest a solution? Thanks in advance. :confused: -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=466177 If you just wish the time to display, set the cell format to Time. If you wish to use the time portion then =A1-Int(A1) will give you...

hours/month excluding weekends (10hr)
I need to calculate weekday hours in particular month; excludin holidays and weekends, based on 10 hr work days -- Message posted from http://www.ExcelForum.com use: Start date of month in A1 End Date of month in A2 # of Holidays (not including weekends) in cell A3 cell A4: =networkdays(A1,A2,A3)*10 HTH, ryanb. "cwren" <cwren.yi2om@excelforum-nospam.com> wrote in message news:cwren.yi2om@excelforum-nospam.com... > I need to calculate weekday hours in particular month; excluding > holidays and weekends, based on 10 hr work days. > > > --- > Message pos...

Re: http://bassturk20.piranho.de/
"rghjbnmj" <196161> ha scritto nel messaggio news:... > > "bayrm" <bozkurt-0@hotmail.de> ha scritto nel messaggio > news:0C307C2E-9B41-4864-8BC6-07947A6C90D9@microsoft.com... >> Bassturk-Online-Games >> Call of Duty: Modern Warfare 2 Geschrieben von: Administrator >> Dienstag, den 17. November 2009 um 20:45 Uhr >> >> Call of Duty: Modern Warfare 2 >> >> Noch immer herrscht im offiziellen Forum von Infinity Ward >> Ausnahmezustand. >> Viele Spieler beschweren sich �ber die fehlenden De...

Get Start date of Week number and Year
I’d like to build the following expression in my query GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate])) So if EnteredDate = 11/3/2009 the function would return 11/1/2009 But GetStartWeekNumber does not exist as an Access Built-In Function. Is there another way to do this as an expression in a query? I’m not familiar with creating my own functions. Thanks. That would depend on how you define the start of the week... One option would be to get the day-of-week number of the date (in my system/setup, Monday is day 2), then subtract one less than that...

Setting up Exchange 2003
I had this problem the other day while extending the schema in preparation for an upgrade to Exchange 2003. I searched through a lot of databases and search engines, and found that a lot of people have had this problem, but none of the forums led to a clear cut answer to the issue. So, I thought I'd share my solution to try and help others out, and save them some time and frustration. I was ready to call Microsoft support right before I figured this out. While running forest prep (setup /forestprep), setup failed with Error code 0XC103798A. It then specified to check the Exchange inst...

How do I sort data by date excluding time
I would like to sort a database by date and transaction type. The problem is the data sorts by time, even though the field is formatted to show date only, with the transaction type not sorting within the date because it shows up in the time order. How do I get rid of the time? If date/time column is A then you have to use an empty helper column filled with formula =INT(A2) and sort by it. -- Regards! Stefi „markd” ezt írta: > I would like to sort a database by date and transaction type. The problem is > the data sorts by time, even though the field is for...

Month view: scroll to see more?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Email Client: Exchange Hello-- At MIT we have recently migrated to Exchange, which seems to be a setback in myriad ways. Does anyone know how to scroll in calendar Month View to see all items on a single day? I need to stay in Month View but have the ability to see all items on each day. I don't want to move to day view. Thanks! ...

Re-exporting functions from dynamically linked DLL
Hi, I have the following situation: I have a 3rd party DLL which does not include a LIB file, just a header file with typedef declarations for exported functions (It is intended for dynamic linking) I need to wrap the DLL to allow for callbacks to my own code (in Lisp), and to do this I need to load the DLL using LoadLibrary and access each function that I need using GetProcAddress. So for example: The third party header will say: typedef INT (WINAPI * lp_function)(); Then in my c wrapper file: #include "3rdparty.h" #ifdef _WIN32 #define DllExport __declspec(dllexport) #else #...