Changing Formats with Macro

In the workbook you say "The Button that fires the Macro should show the status and condition". I assume "status" is "DATE", "TIME", etc. If so what is "condition"?

The workbook also says "I need a macro that changes the Start and Stop Date/Time  Format parameter to Format in DATE/TIME (which it is now), DATE ONLY, to TIME ONLY, to TIME HOUR to TIME MINUTE" which is different than what your question asks for. For example there's no mention of "Date: Yr".

This macro does what I believe the question asks for. Assign it to the button and let me know if you need anything changed.

Sub ChangeFormat()
    With Range("C4")
        Select Case .NumberFormat
            Case "m/d/yy h:mm;@" ' DATE/TIME
                .NumberFormat = "yyyy"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Yr."
            Case "yyyy" ' DATE YR
                .NumberFormat = "mm/yyyy"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Yr."
            Case "mm/yyyy" ' DATE MO YR
                .NumberFormat = "ddd"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Day"
            Case "ddd" ' DATE DAY
                .NumberFormat = "hh:mm"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Time: Hr./Min."
            Case "hh:mm"
                .NumberFormat = "hh:mm:ss"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Time: Hr./Min./Sec."
            Case "hh:mm:ss"
                .NumberFormat = "m/d/yy h:mm;@"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date/Time"
        End Select
        Range("C9").NumberFormat = .NumberFormat
    End With
End Sub

Select allOpen in new window

Martin,

Thanks!  I put your code in and linked the Macro.  Two challenges.  I get an parameter error on the macro.  And it looks like it does change the format in C4 but not in the other cell.  

Secondly, can you tell me how Fanpages wrote this macro?  There is a Class Module I am unfamiliar with and when I change the Tab name, it doesn't completely change.  There are no hidden WSs and I see little code here that I understand.  Can you give me some insight on what I have?

Thank you,

B.
Time-Interval-Frequency-calculationv4.xl

His dblDateAdd function is a wrapper around the built-in DateAdd function, and as far as I can see it doesn't do anything that you can't do with a simple line of code. The parts of DateAdd are the interval, the number to increment by or decrement by and the date, so for example

Range("C9").Value = DateAdd("m", 6, Range("C4"))

Select allOpen in new window

where the interval is "m" (months), the increment is 6 and the date is in C4 will add 6 months to the date in C4 and put it in C9.

The DateAdd function is described here.

I've requested that this question be closed as follows:

Accepted answer: 0 points for Bright01's comment #a40927312

for the following reason:

Martin,  Great work!  Thanks for helping out.

I do have a question for you.  I am not favoring a call to a built in function given that as I use and distribute the model, I think it adds complexity.  Does everyone have access to this function in Excel or is it an add-in for example?   I had originally thought to use an Array to provide the relationship between 2 different Time/Date/Interval/Frequency elements.  

What are your thoughts about that?  Should I author another question trying to gain some simplicity?  For this particular Application, I'm going to be authoring about 3 more questions since this eventually turns into a very nice graphic.

Thoughts?

B.

I think the Author meant to assign the points to my post  40927327.

Martin:

His dblDateAdd function is a wrapper around the built-in DateAdd function, and as far as I can see it doesn't do anything that you can't do with a simple line of code.

The function is, in reality, just two lines of code, with error checking around them, that could have been one statement, but I split it into two to avoid confusion with the adjustment depending on the MS-Excel workbook-specific "Date 1904" setting.

It is wise not to make assumptions on the usage of this setting.

In case you missed this in the original code listing, here are the two pertinent statements:

  dblReturn = CDbl(datValue + (1462# * ActiveWorkbook.Date1904))    ' 1462 = (365 * 4) + 2
  dblReturn = DateAdd(strInterval, dblNumber, dblReturn)

Usage in-cell is passing the first character of cell [C6] (as this will define the interval as "Second", "Minute", "Hour", "Day", "Week", "Month", or "Year"), the interval quantity value in cell [C5], & the starting date in cell [C4]:

=dblDateAdd(LEFT(C6,1),C5,C4)

This said, Bright01, thinking about this now, did you test the previous solution for both "Minute" & "Month"?

I think there may be an issue there (as both begin with "M").

I suggest you change cell [C9] from:
=dblDateAdd(LEFT(C6,1),C5,C4)

to:
=dblDateAdd(IF(C6="Minute","n",LEFT(C6,1)),C5,C4)

100% of the credit for this answer goes to Martin Liss.  Sorry Martin, the travel must be getting to me.  In China now headed to US on Monday. Thanks so much to both you and Fanpages for the help here.  I will be posting another question related to this project.  Also, Fanpages, I made the change you suggested.  My question still stands however; given I'm calling on a public class function, will everyone who runs Excel 2010 be able to use this capability?  I hope so....otherwise I need the lines of code that are easier to understand and I can integrate this capability more easily.

Again, thank you both.

B.

Fanpages,

I found an error in the calculations.  See uploaded file and cell C9.

Martin,

If I wanted to add another format, would this be correct for Date: Mo. Day. Yr.?

Case "mm/ddd/yyyy" 'Date MO DAY YEAR"
                .NumberFormat = "mm/ddd/yyyy"
                .ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Day Yr."

Thanks again guys....just trying to tweak the model and learn at the same time.

B.
Time-Interval-Frequency-calculationv3r.x

I think there may be an issue there (as both begin with "M").

You should use 'n' as the interval for a minute.

Martin,

If I wanted to add another format, would this be correct for Date: Mo. Day. Yr.?

Case "mm/ddd/yyyy" 'Date MO DAY YEAR"
                .NumberFormat = "mm/ddd/yyyy"
                .ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Day Yr."

Try it and let me know if there's a problem.

Martin:

You should use 'n' as the interval for a minute.

Yes, as I suggested in the same comment; Comment ID: 40928358.

Bright01:

Revised formula for cell [C9]...

Please change (the original formula):
=dblDateAdd(LEFT(C6,1),C5,C4)

or (the first revision mentioned above):
=dblDateAdd(IF(C6="Minute","n",LEFT(C6,1)),C5,C4)

to:
=dblDateAdd(IF(C6="Minute","n",IF(C6="Year","yyyy",LEFT(C6,1))),C5,C4)

Thank you.

(I have attached an updated workbook to the original question thread).

Martin,

I broke it.

B.
Time-Interval-Frequency-calculationv5.xl

OK here's the correct code. Lines 8 to 12 are your new code including the change I made to line 8. Line 11 was changed (it was what broke your attempt I suspect) because ".ActiveSheet" was trying to say incorrectly that ".ActiveSheet" was a property of Range("C4") just like ".NumberFormat" is a property of Range("C4"). I changed line 14 so that the cycling would work.

Note that in line 10 you have  .NumberFormat = "mm/ddd/yyyy". That will give results like "08-Sat-2015" which is a little odd. If instead you want  "08-01-2015" change both lines 10 and 15 to "mm/dd/yyyy" (one less "d").

Sub ChangeFormat()
    With Range("C4")
        Select Case .NumberFormat
            Case "m/d/yy h:mm;@" ' DATE/TIME
                .NumberFormat = "yyyy"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Yr."
                
'            Case "mm/ddd/yyyy" 'Date MO DAY YEAR"
            Case "yyyy" 'Date MO DAY YEAR"
                .NumberFormat = "mm/ddd/yyyy"
'                .ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Day Yr."
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Day Yr."
                
'            Case "yyyy" ' DATE YR
            Case "mm/ddd/yyyy" ' DATE YR
                .NumberFormat = "mm/yyyy"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Mo. Yr."
            Case "mm/yyyy" ' DATE MO YR
                .NumberFormat = "ddd"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date: Day"
            Case "ddd" ' DATE DAY
                .NumberFormat = "hh:mm"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Time: Hr./Min."
            Case "hh:mm"
                .NumberFormat = "hh:mm:ss"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Time: Hr./Min./Sec."
            Case "hh:mm:ss"
                .NumberFormat = "m/d/yy h:mm;@"
                ActiveSheet.Shapes("CycleFormat").TextFrame2.TextRange.Characters.Text = "Date/Time"
        End Select
        Range("C9").NumberFormat = .NumberFormat
    End With
End Sub

Select allOpen in new window

Martin,

I must be doing something very stupid.  I put the changes in..... line 14 is texted out in the code.....if that is the line fixed, shouldn't it not be texted out?

Here is the latest with your code changes.

B.
Time-Interval-Frequency-calculationv5.xl

Line 14 is commented out only because that was what was there before I added your new Case, and I left it there for documentation purposes. You can delete all the commented out lines in that sub. Is there anything else you need?