Time/Frequency/Date Error correction

EE Pros,

I have a nice Macro that Fanpages and Martin Liss have been contributing to that needs a correction.  The selection of "Weeks" in Frequency is apparently applying "Days" not "Weeks" to the calculation.  

Worksheet Attached.

B.
Time-Interval-Frequency-calculationv5.xl
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
As shown here the parameter for "week" is "ww" not "w" which I assume you were trying to use. Also as I pointed out in another thread you can just do this to add 1 week.


Range("C9") = DateAdd("ww", 1, Range("C4"))
Bright01Author Commented:
Martin,

Where do I add this 1 line?  I put it at the end but it messed up the formula in C9.

B.
Martin LissOlder than dirtCommented:
Rrmove the formula from C9. If you are going be updating it via code you can't have both.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Bright01Author Commented:
I got it. However when you change the interval amount it doesn't reflect it as part of the change in C9. So if I remove the formula from C9, which is fine, and change the number of intervals in cell C5, it does not update c9.

B
Martin LissOlder than dirtCommented:
It should have been obvious to me that you originally had a formula there but I also never realized the dblDateAdd was a UDF and you were using it there. It should only take a minor change to that formula to get it to working for you and I'll do that right after I eat breakfast.
Martin LissOlder than dirtCommented:
It was easier for me to write my own UDF.

Usage as a formula in C9
Note: The formula never has to change unless you change the location of the data.
=DateAddInterval(C4,C6)
Function DateAddInterval(dte As Range, Interval As Range) As Variant
    Dim strInterval As String
    
    Select Case Interval
        Case "Second"
            strInterval = "s"
        Case "Minute"
            strInterval = "n"
        Case "Hour"
            strInterval = "h"
        Case "Day"
            strInterval = "d"
        Case "Week"
            strInterval = "ww"
        Case "Month"
            strInterval = "m"
        Case "Year"
            strInterval = "yyyy"
    End Select
    DateAddInterval = DateAdd(strInterval, dte.Offset(1, 0), dte)
End Function

Open in new window

[ fanpages ]IT Services ConsultantCommented:
^ "It was easier for me to write my own UDF."

...but still you are missing the relevance of the "Date 1904" system setting, as discussed previously.

If you do not include this, the end user's runtime environment may produce incorrect values using DateAdd(...).
Bright01Author Commented:
Martin,

Thanks for the update.  Where do I put in the Function you provided and then simply add the new formula in?  Do I substitute it for the "Change Format" Sub?

Please advise...."thank you"!

B.
Martin LissOlder than dirtCommented:
Where do I put in the Function you provided
Put it in any module. I assume you know how to do that but just in case...

In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window). If you don’t see an existing module then select Insert -> Module from the context menu. Otherwise just select the module.

Copy the Function (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

and then simply add the new formula in?  
Yes, replace the =dblDateAdd(IF(C6="Minute","n",IF(C6="Year","yyyy",LEFT(C6,1))),C5,C4) formula with =DateAddInterval(C4,C6)

Do I substitute it for the "Change Format" Sub?
No. I've attached an updated workbook.
28706077.xlsm

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
Bright01Author Commented:
Martin,

Thank you!  Works great.  Well done.

Will be posting more.

B.
Bright01Author Commented:
Martin,

Very sorry to keep bothering you but as I was testing the macro, making changes to the variables (in cells C4 through C7), I found out that the formula in cell C9 does not update.  Can you test it and see what is wrong?

Thank you,

B.
Bright01Author Commented:
Martin,

 Very sorry to keep bothering you but as I was testing the macro, making changes to the variables (in cells C4 through C7), I found out that the formula in cell C9 does not update.  Can you test it and see what is wrong?

 Thank you,

 B.
Martin LissOlder than dirtCommented:
Give me a specific case of what you did and what you expected to happen.
Bright01Author Commented:
Martin,

Here is an example.... if you change the # of intervals, the # of frequencies updates but not the Stop date.

b.
D--Data-Data-Temp-Error-in-Change.pptx
Martin LissOlder than dirtCommented:
Replace the UDF with the following:

Usage: =DateAddInterval(C4,C5,C6)
Function DateAddInterval(dte As Range, Intervals As Range, IntervalType As Range) As Variant
    Dim strIntervalType As String
    
    Select Case IntervalType
        Case "Second"
            strIntervalType = "s"
        Case "Minute"
            strIntervalType = "n"
        Case "Hour"
            strIntervalType = "h"
        Case "Day"
            strIntervalType = "d"
        Case "Week"
            strIntervalType = "ww"
        Case "Month"
            strIntervalType = "m"
        Case "Year"
            strIntervalType = "yyyy"
    End Select
    DateAddInterval = DateAdd(strIntervalType, Intervals, dte)
End Function

Open in new window

Bright01Author Commented:
Martin,  Much thanks!   Seems to work fine now.

B.
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.