1st part works; how to get an actual date dumped? MitchellWMA <mitchellwma1[ at ]yahoo.com> 12/30/2008 3:50:01 PM 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

Re: 1st part works; how to get an actual date dumped? MitchellWMA <mitchellwma1[ at ]yahoo.com> 12/30/2008 3:52:05 PM (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)

Re: 1st part works; how to get an actual date dumped? JE McGimpsey <jemcgimpsey[ at ]mvps.org> 12/30/2008 4:02:04 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[ at ]k36g2000yqe.googlegroups.com>, MitchellWMA <mitchellwma1[ at ]yahoo.com> wrote: [Quoted Text] > 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

Re: 1st part works; how to get an actual date dumped? MitchellWMA <mitchellwma1[ at ]yahoo.com> 12/30/2008 4:43:00 PM Perfect, thanks. Works like a charm. ::g::

Re: 1st part works; how to get an actual date dumped? "Shane Devenshire" <shanedevenshire[ at ]sbcglobal.net> 12/30/2008 4:59:32 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[ at ]yahoo.com> wrote in message news:86dcfce8-04f3-4165-9dfb-ef8ebcf312af[ at ]k36g2000yqe.googlegroups.com... [Quoted Text] > 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

Re: 1st part works; how to get an actual date dumped? MitchellWMA <mitchellwma1[ at ]yahoo.com> 12/31/2008 2:52:23 PM On Dec 30, 11:59 am, "Shane Devenshire" <shanedevensh...[ at ]sbcglobal.net> wrote: [Quoted Text] > Hi, > > Sub MondayStartDate() > ActiveSheet.Unprotect >     [L2].Value =2-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