Changing Formats with Macro

EE Pros,

Fanpages helped me with building out a array that identifies the relationship between Time/Date, Interval/Frequency and displays an end date and a number of data collection points.  Due to the application of this spreadsheet, I need to have a macro that can fire to change the Time/Date Format that is in the WB now, to a more granular view.  So, by firing the macro, I need the date formats to change from Time/Date (Mo./Day/Yr., Hr., Min. Sec.) ((THIS IS THE CURRENT FORMAT), to Date: Yr. (e.g. 2013, 2014, 2015), Date: Mo. Yr. (e.g. 04/2013, 05/2015, etc.),  Time: Day (e.g. Mon, Tues, etc.), Time: Hr./Min. (e.g. 03:35, 14:28, Time: Hr./Min./Sec.).  To be able to see the status in the button or to the side would be nice.

Sample Attached.

Thank you in advance,

B.
Time-Interval-Frequency-calculationv4.xl
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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"?
0
Martin LissOlder than dirtCommented:
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".
0
Martin LissOlder than dirtCommented:
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

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Bright01Author Commented:
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
0
Martin LissOlder than dirtCommented:
I'm sorry. I forgot to mention that in my testing I changed the name of the button to "CycleFormat". When I did that in your new attachment and added my macro it works without error. Give it a try.  Let me look now at Mr. fanpages' macro.
28704811.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
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"))

Open 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.
0
Bright01Author Commented:
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.
0
Martin LissOlder than dirtCommented:
I think the Author meant to assign the points to my post  40927327.
0
[ fanpages ]IT Services ConsultantCommented:
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)
0
Bright01Author Commented:
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.
0
Bright01Author Commented:
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
0
Martin LissOlder than dirtCommented:
I think there may be an issue there (as both begin with "M").
You should use 'n' as the interval for a minute.
0
Martin LissOlder than dirtCommented:
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.
0
[ fanpages ]IT Services ConsultantCommented:
Martin:
You should use 'n' as the interval for a minute.

Yes, as I suggested in the same comment; Comment ID: 40928358.
0
[ fanpages ]IT Services ConsultantCommented:
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).
0
Bright01Author Commented:
0
Martin LissOlder than dirtCommented:
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

Open in new window

0
Bright01Author Commented:
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
0
Martin LissOlder than dirtCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.