1st part works; how to get an actual date dumped?

I was able to put together 2 macros and am pleased with results.  When
I click on a button that has the macro below assigned to it, it puts
the date for this week's Monday into L2 which shows the beginning of
the week and which then affects all the rest of the dates in the sheet
that are dependent on L2:

**********************************
Sub MondayStartDate()
ActiveSheet.Unprotect
    Application.Goto Reference:="R2C12"
    ActiveCell.FormulaR1C1 = _
        "=2-WEEKDAY(TODAY())+TODAY()"
    Range("L2").Select
ActiveSheet.Protect
End Sub
********************************

However, the formula is what gets dumped into L2.
i.e., L2 currently shows:

=2-WEEKDAY(TODAY())+TODAY()

rather than:

2008.12.29

I don't know how to get the formula to do the switch.  How do we do
that please?  thx
0
12/30/2008 3:50:01 PM
excel 39879 articles. 2 followers. Follow

5 Replies
372 Views

Similar Articles

[PageSpeed] 15

(Oops sorry, thought I was in the programming ng.  Forgive wrong ng
post.  I'll just leave it at that and not re-post in right ng.  Hope
that's ok.  thx)
0
12/30/2008 3:52:05 PM
One way:

    Public Sub StartDate()
        With ActiveSheet
            .Unprotect
            .Range("L2").Value = 2 - Weekday(Date) + Date
            .Protect
        End With
    End Sub

In article 
<86dcfce8-04f3-4165-9dfb-ef8ebcf312af@k36g2000yqe.googlegroups.com>,
 MitchellWMA <mitchellwma1@yahoo.com> wrote:

> I was able to put together 2 macros and am pleased with results.  When
> I click on a button that has the macro below assigned to it, it puts
> the date for this week's Monday into L2 which shows the beginning of
> the week and which then affects all the rest of the dates in the sheet
> that are dependent on L2:
> 
> **********************************
> Sub MondayStartDate()
> ActiveSheet.Unprotect
>     Application.Goto Reference:="R2C12"
>     ActiveCell.FormulaR1C1 = _
>         "=2-WEEKDAY(TODAY())+TODAY()"
>     Range("L2").Select
> ActiveSheet.Protect
> End Sub
> ********************************
> 
> However, the formula is what gets dumped into L2.
> i.e., L2 currently shows:
> 
> =2-WEEKDAY(TODAY())+TODAY()
> 
> rather than:
> 
> 2008.12.29
> 
> I don't know how to get the formula to do the switch.  How do we do
> that please?  thx
0
jemcgimpsey (6723)
12/30/2008 4:02:04 PM
Perfect, thanks.  Works like a charm.  ::g::
0
12/30/2008 4:43:00 PM
Hi,

Sub MondayStartDate()
ActiveSheet.Unprotect
    [L2].Value =2-WEEKDAY(Date())+Date()
ActiveSheet.Protect
End Sub

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"MitchellWMA" <mitchellwma1@yahoo.com> wrote in message 
news:86dcfce8-04f3-4165-9dfb-ef8ebcf312af@k36g2000yqe.googlegroups.com...
> I was able to put together 2 macros and am pleased with results.  When
> I click on a button that has the macro below assigned to it, it puts
> the date for this week's Monday into L2 which shows the beginning of
> the week and which then affects all the rest of the dates in the sheet
> that are dependent on L2:
>
> **********************************
> Sub MondayStartDate()
> ActiveSheet.Unprotect
>    Application.Goto Reference:="R2C12"
>    ActiveCell.FormulaR1C1 = _
>        "=2-WEEKDAY(TODAY())+TODAY()"
>    Range("L2").Select
> ActiveSheet.Protect
> End Sub
> ********************************
>
> However, the formula is what gets dumped into L2.
> i.e., L2 currently shows:
>
> =2-WEEKDAY(TODAY())+TODAY()
>
> rather than:
>
> 2008.12.29
>
> I don't know how to get the formula to do the switch.  How do we do
> that please?  thx 

0
12/30/2008 4:59:32 PM
On Dec 30, 11:59=A0am, "Shane Devenshire"
<shanedevensh...@sbcglobal.net> wrote:
> Hi,
>
> Sub MondayStartDate()
> ActiveSheet.Unprotect
> =A0 =A0 [L2].Value =3D2-WEEKDAY(Date())+Date()
> ActiveSheet.Protect
> End Sub

[snip]

Shane, hi!  Is there any reason to use one formula over the other?
I'm curious as to the difference.  thx
0
12/31/2008 2:52:23 PM
Reply:

Similar Artilces:

Working with templates.
How do I create "different" binder spines on a template page? Are the binder spines are all the same size? Any publication you create can be saved as a template. If you only want a page of templates, it will be the same as if you were creating labels. Binder templates here: http://office.microsoft.com/en-us/templates/results.aspx?qu=spine&sc=4&av=ZPB If you want every binder different you will have to layout your page. Download the binder template, make a note of the margins and gaps, recreate this in Arrange, Layout Guides. -- Mary Sauer http://msauer.mv...

After upgrade, OWA2003 'change password' not working
My OWA2000 box was configured to let people go to options and change password from there. After upgrading to Exch2003 SP1, OWA 2003 no longer let people change password there. I go to "Options" folder in OWA2003 and I don't even see the button to let me change password. What do I need to do to setup the password feature again ? http://www.petri.co.il/enable_password_changing_through_owa_in_exchange_2003.htm http://support.microsoft.com/kb/297121 Both links have good instructions on how to do this. "Marlon Brown" wrote: > My OWA2000 box was configured to let p...

How to get all records in a Query even you select a record from a conbox box
Hello there well I have a big probem I am trying to create a query that I can select a month using a combo box in a form and returns with all the record and not ignoring the rest of the months for example If I have records for July it will come only july's except the other month I prefer to get all the months including july's I now is going to make duplicate in July but I don't care that is what I need I used is null in the criteria and it works just fine but I notice that if I Select the month of august all the records from july's are gone only shows august anyway here is a ex...

Email display ACTUAL From:, not persons name?
Outlook 2003 sp2... I receive emails from certain businesses which sometimes winds up in the Junk E-mail folder. I need to be able to easily see the REAL email address rather than the name of the person sending the email. These businesses have many different people working there and I do not know their names. But I would recognize the REAL email address as being from a business where I want to read the email. Also I get junk email in my inbox which looks like email I would want to read, but one look at the REAL email address and I would know it is junk. The "From:" field in ...

ActiveWorkbook.Application.ScreenUpdating = False Not Working
have a very long procedure which does many things. I set the first lin of my code to ActiveWorkbook.Application.ScreenUpdating = False Usually when I have done this the user does not see all the scree flickering and the actions of your procedure but for this one you se all of that. How can I set this so that the user does not see all o the Screen Updating and when they click All Done they can continu working? Thanks ActiveWorkbook.Application.ScreenUpdating = False Application.DisplayAlerts = False ActiveSheet.Unprotect Application.Run ("MoveLongQuoteData") Sheets(Array("Fin...

Spell check not working 2007, 2010
All the appropiate check boxes etc are turned on. I can type a spelling mistake, hit proof and spell check and it says all ok.. no as you type functionality working either. help please ! ...

Worksheet Calc Gets Stopped When Sheet is Clicked
The worksheet I'm working with has thousands of formulas, so if the calculation stops, I need to restart the entire process. If I even attempt to access a menu item or drag the window around on the screen, even minimize the window, all calcs stop. Is there anyway to protect the spreadsheet from being interrupted so easily? I don't think so. In fact, I think many depend on this feature. Instead of waiting for the calculation process to finish, the user can just go to the next cell and start inputting more data. You may want to turn calculation to manual so xl doesn't even try...

import or query another db to get weekly data for a report
I realize I might not need an import because I don't want to write over the imported data each week. Do I use a SQL update or append query to get data from an Access db on our network? I don't see the tables in my query design tool so I'm wondering how I do the query if I can't see the tables for the other database. thanks, ...

Getting EOY Time
Here's my Problem: I have monthly sheets that keep tabs of production time for various colors of widgets. I have 12 sheets one for each month. Each Identical to the other. I have an EOY (End of Year) sheet, also identical which I want to use not only for EOY but also to check how we are doing YTD. Keeping tabs on columns of widgets is easy summing thru the cells on each workesheet =sum(Jan:Dec!d4). However getting the running clock total is not as easy. I don't want to do =average() because that would be averageing averages. I can get at the # I want by putting the month out on a flat...

can I add 4/5 and1/2 to get 6/7 as in 4 of 5 + 1 of 2 and get 6 o
I am trying to keep a running total by month of sales vs calls and the data is entered as 4/5, etc. Is this possible in Excel? Hi try =TRIM(LEFT(E2,FIND("/",E2)-1))+TRIM(LEFT(F2,FIND("/",F2)-1))&"/"&=TRIM(RIGHT(E2,FIND("/",E2)-1))+TRIM(RIGHT(F2,FIND("/",F2)-1)) "kschmoll" wrote: > I am trying to keep a running total by month of sales vs calls and the data > is entered as 4/5, etc. Is this possible in Excel? Yes, entering 4/5 is a possible way of entering either 4th May or 5th April, depending on you...

Retrieve Records by Date
My underlying table has a field called Date. In my main form, I would like to have several buttons called 2005, 2006, 2007 and 2008. If, for example, a user clicks on the 2007 button, I would like a form to open with all the records from 1/1/2007 to 12/31/07, and so forth with the appropriate year. What code might I use on the click event to accomplish this? Thanks in advance. Janna "Janna" <Janna@discussions.microsoft.com> wrote in message news:E3A62379-C8D2-4EF8-9EF4-48CE3F49B078@microsoft.com... > My underlying table has a field called Date. In my main form, I...

Lay Away Expiry Date
Hi There Is there a way of printing lay away expiry/due date on receipts. -- Cheers Topi This is a multi-part message in MIME format. ------=_NextPart_000_00E6_01C932BD.52633190 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Topi, Try here http://tinyurl.com/4gdbu Receipt - 40 column with Expiration = Date Must have access to CustomerSource --=20 Jeff=20 Check Point Software Search this newsgroup -- http://tinyurl.com/2lmk4w (Stolen from DRS) =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3...

Activities Tab Does Not Work
The functionality of the 'activities" tab within contacts no longer exists. I went into the folders >> contacts >> properties >> and reset all the activities within this tab; i.e. linked journal entries to the journal folder But no luck My address book and contacts folders are fine If I open up the journals folder directly sure enough the journal entries have links to the proper contacts....so the links DO exist ! However the activities tab within a contact still says that my folders need to be reset !! Any ideas are welcome -- Adam ...

Access with Switchboard does not want to work.
I have 2 Access databases that are supposed to open up to a Main Switchboard. When I try though I get the following error: "Compile Error: Error Loading DLL" It is listed showing the following items with the word ".Connection" highlighted. They both worked last week. Now I can't get any switchboards to work. Thanks ' Open the table of Switchboard Items, and find ' the first item for this Switchboard Page. Set con = Application.CurrentProject.Connection stSql = "SELECT * FROM [Switchboard Items]" ...

Monitoring and raporting console do not working properly
I have a problem with Monitoring and reporting Generally this tool works OK but I do not have all information in it. There are informations about errors and space on hard drives. In other section I have information that: there are no data, please check if service WMI is running. I checked hundred times and this service is working. I have done this steps, already. 1. Check if wmiprvse.exe and provDisk.dll already exist on server. There is wmiprvse.exe on server but provDisk.dll is missing. 2. Register provDisk.dll Run "regsvr32 %path%provdisk.dll" There is n...

Domain Users Rules dont work
Windows 2000 Pro Outlook 2002 when a user creates a rule it only creates a clint side rule and will not work unless they go into Rules Wizard and run the rule. I am able to create rules but I am a member of the admin group. Seems like some permission problems. Can anyone give me a suggestion? Thanks in advance Windows 2000 Server AD Windows 2000 Pro, office 2002 Pro what is the rule? some rules are client side and others are server side - while you can force some server rules to the client, you can't force clients to be server rules. http://www.slipstick.com/rules/serverbased.ht...

Outlook Address Book Not Working But Contacts Is Working
I tried to research this problem before typing this, but all the answers are for IMO so I will ask... In Outlook 2003, I can go to CONTACTS and all of my contacts are there in separate subfolders I created. When I type an e-mail and click on TO and the SELECT NAMES window comes up, nothing is there. All it says in the drop down box is Outlook Address Book. So I am thinking there must be a way to repair the Outlook Address Book with all the information in CONTACTS which is all there and correct. I ran SCANPST.EXE and that didn't fix anything. In Outlook, I can go to TOOLS > E...

after insertion of a subproject in the masterproject without links, the dates move
I took the copyd information out of another thread with another topic, so that the heading fits to the content. --- > However, when i insert subprojects in my masterproject without link, > then the dates move. I tryed it several times. > It also happens when i remove the exisitng linkage. E.g. i insert a > project and link it. Everything is fine then. When i now remove the > linkage, then the dates move :( how can i stop that? > > thx a lot, rgds manuel -------------------- Hello manuel, If you insert projects into a master project without links, th...

Getting total pages per group/section
Hello, I am trying to include total # of pages per group on a SSRS report. For example, if the SRS reports has parameters customer ID and document date, it will print all invoices for that customer(s) for the date. For each page of the form, we would like to include page # + total # of pages, such as 1/3, 2/3, 3/3, 1/2, 2/2, 1/4, 2/4, 3/4 and so forth. I can get the page # with some coding, but I am unable to find a way to get the total # of pages per group. I’ve tried in both SQL 2005 and 2008. The only way I found is to “preprocess the report in a silent run”, but ...

How do I get text to permanently flash?
Excel 2000. Need text to flash at all times for a display screen. Has to be excel as several calculations also required Don't do it. People will hate you for it. -- HTH RP "garym" <garym@discussions.microsoft.com> wrote in message news:8D1EDD3E-14DD-42E6-A703-12291DE3B377@microsoft.com... > Excel 2000. Need text to flash at all times for a display screen. Has to be > excel as several calculations also required I owuld love to know this to... the only thing I can think of is som kind of loop in VB that deletes and reenters teh text over and over.. I know...

Work order problems
Hi, We use RMS (SQL 2000) in a retail environment as well as a POS environment, although this is obviously not recommended. In the POS environment, we use work orders to keep a "tab" of the client's bill until it is eventually tendered off. On a few occasions, we have had some weird things happen with work orders. On a few occasions, open work orders have managed to disappear whilst being saved. I.e. When the waiter adds more items to the work order and saves it, it just disappears. Other times, work orders have frozen up while the waiter was picking up the entire work ...

Getting enumerations from an XSD file
I am using an XSD to validate XML files. Within the XSD, I am storing some simple types which are enumerations. In addition to using these XSDs to validate XML, I would like to access the enumerations to populate some controls on some pages. Is there any way to programatically get the enumeration values within the XSD in my c# code??? thanks in advance!!! Todd -- Thanks in advance, Todd Hi Todd, ofcourse, the xsd is just another xml structure. You can load the enumeration elements using the XmlTextReader, XPathDocument and XPathNavigator classes. Msdn has fairly good documentat...

Changing posted date on a sales invoice
Hi, Is there a way of changing the posting date on a sales invoice? I know we cannot do through the GP interface but was hoping for a solution on the back end. We are trying to avoid voiding the batch and redo all invoices. Please advice. Vishal, Change the GLPOSTDT in SOP10100. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www.gp2themax.blogspot.com Thanks Frank, I appreciate the response. We logged a case with MS for this and here is an excerpt from their response. At this point we've decided to void and re-enter. Hopefully someone fi...

Integration
I'm creating an integration where I do a regular GL integration, but in the After Document script, I need to make a change to one of the fields in the database for that record. I need to somehow grab the new Journal Entry number generated when the document is created to use in the SQL UPDATE statement. Any ideas are appreciated! This method is not fail-safe, but it's the closest you will get. In the before document script you may want to open an ADO connection and read the next journal entry from the GL40100 table. You can assign this to a global variable with the SetVariable f...

Unable to get email w/o entering user name / passwrd each time
I recently reloaded Outlook 2000 on to a new PC machine running WIndows XP - home edition. I configured a new email account using standard procedures, now everytime I want to send / receive email I'm prompted to enter my user name and password. Even though all the (save password) boxes are checked, Outlook requires me to re-enter each time. I'm also encountering the same problem on select websites where the user name / password has been selected for memory. Any ideas? ...