Learn the most important control and control categories that every architect and developer should include in their projects.

EE Pros,

I am trying to build an Array that relates different elements of time (Sec., Min., Day, Month, Year) so that if you select "an INTERVAL, you get the relationship applied against a selected "Start Date/Time" to produce an "End Date/Time" and by also selecting a FREQUENCY Measure, you will get the number of data points collected in that Time Frame.

I have posted a mocked up version with comments for your review.

Thank you in advance,

B.

TIME-FREQUENCY-ARRAY.xlsm

I am trying to build an Array that relates different elements of time (Sec., Min., Day, Month, Year) so that if you select "an INTERVAL, you get the relationship applied against a selected "Start Date/Time" to produce an "End Date/Time" and by also selecting a FREQUENCY Measure, you will get the number of data points collected in that Time Frame.

I have posted a mocked up version with comments for your review.

Thank you in advance,

B.

TIME-FREQUENCY-ARRAY.xlsm

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

Thank you! I think you are on the right track; and I'm A-OK with using a Macro (VBA) rather then an Array and formulas.........

However, the numbers are not quite sequenced correctly. Perhaps some definitions are in order;

1.) The Interval is the length of time that we are going to take measurements. So if it is "2" and DAYs, then the length of the sample would be just that.

2.) The Frequency is the sub or super set of how much data (data points) we are collecting WITHIN the Interval. So if I selected "1" and "Days" (as the Interval Metric), and Frequency in "Hours", the result would be 24.

3.) This should also work if I select Frequency as Weeks and the reading would be 1/7. For the interval of "1" and "Days" and the fact that our Frequency is actually in "Weeks".

Finally, you have the Date/Time Stop cell (C9) correct. It's the # of Data Points (C10) that is not showing up correctly.

I hope that is an easy and clear explanation. Again, thank you for your assistance.

B.

I have been looking over this and have found only one error and that is in the formula in C10. This may be the reason why I need an Array (or at least thought so). When you select an Interval metric and a Frequency and number of Intervals, it is the intersection of the Interval value created by the selection of the Metric X number of Intervals and the relationship of the Frequency Measure that should produce the result for C10.

So, for example, and in its simplest form, if you pick NUMBER OF INTERVALS = 1, and INTERVAL VALUE = Days, then Frequency Measure = Hours, you would get 24. If you pick NUMBER OF INTERVALS = 1, and INTERVAL VALUE = Days, then Frequency Measure = MINUTES, you would get 1440 (i.e. 1 Day = 24 Hrs. X 60 MIN./HR = 1440. And so on.

Make sense?

Thanks,

B.

Yes, I think I see what you are trying to achieve.

I presume your previously provided table ("matrix"), "Time Relationship Array", needs to be updated to complete the missing entries.

Please can you do that with an updated attachment, & also offer some further sample input (differing variations of all three input parameters) together with their associated expected output?

I will then change the formula for cell [C10] to accommodate the results, proving the formula with your examples.

Thank you.

I don't know how to build out that array. That was my original problem. I started to but quickly realized I didn't know how to set it up.

B.

Can you please look at providing a few example input value combinations, with a range of differing values for...

[B5] # of Intervals

[B6] Interval Metric

[B7] Frequency Measure

...& your expected output within cell [C10] "# of Data Points"?

We can then either work on the table, or any other 'Expert' can then contribute to the solution if they wish to do so.

Thank you.

# of Intervals = 2

Interval Metric = Days

Frequency Measure = Minutes

This means that the scope or entire time of data collection is 2 Days, translated into number of minutes (as the data points collected) in those 2 Days. So the math/array would calculate the relationship between the Day and the Minutes; so 60 X 24 = 1440 X 2 days = 2880 points of data in 2 days of time (Interval).

Another example;

# of Intervals 3

Interval Metric = Months

Frequency Measure = Days

So there are 7 days (may be fractional so it can be changed based on the value) in 1 week and 4 weeks in 1 month, so 7 X 4 = 28 X 3 = 84 Data Points in 3 months, with Data Points being in Days.

As you can see in an Array, we could calculate seconds in a multiple years if needed. The Array should be able to be "tweaked" to more exact calculations based on more precision on the Time Values (e.g. Days in a month may be 30.3 and not 28).

Does that help?

B.

I have attached an updated workbook.

The revised calculation in cell [C10] (based on the "Revised Time Relationship Array" I have included) is as follows:

=C5 * OFFSET($B$26,MATCH(LEFT(TR

Please can you re-review, & then advise if you think we now have what you were looking for?

Thanks.

(I have added some notes at pertinent areas within the workbook to show how the solution was reached)

Q-28703272b.xlsm

Thank you very much! When I traveled on Sat. from Hong Kong to Beijing, I didn't realize that in Mainland China, Expert Exchange is not accessible. Not sure why. I'm linked to EE via my cell phone hot spot on a train traveling to XIAN and have a few minutes to download your fix and test it out. I'll be back with you as soon as possible. Your patience, capabilities and professionalism are greatly appreciated.

B.

I have added an example; and I'm not getting any results in C10. Is there a way to complete the Array so that whatever Interval, Interval Metric and Frequency we choose, we can get the answer in C10?

I think we are close. C9 is calculating correctly I believe.

B.

Time-Interval-Frequency-calculationv2.xl

Assuming you saw the formula in cell [C10] I posted most recently did work within the workbook I attached, in your recent attachment you have changed the "Frequency Measure" (cell [C7]) drop-down list entries to state singular forms (that is, "Days" is now "Day", & "Months" is now "Month", & so on). The "Interval Measure" (cell [C6]) entries) are also now singular (rather than in a plural form).

My previous formula was removing the "s" from the end of each entry to comply with the naming convention within the "[Revised] Time Relationship Array" headings.

Please change cell [C10] from:

=C5*OFFSET($B$15,MATCH(LEF

to

=C5*OFFSET($B$15,MATCH(C6,

I see the result of 42 in cell [C10] after making this change within your most recent attachment.

PS. The in-cell formulae within the range [F6:G11] that I added just to show how the result was reached, just for information, are now showing #N/A because of the amendments to the position of the "Revised Time Relationship Array" within your attachment.

Simply clear the contents of the range [F6:G11] if you are happy with the result in cell [C10].

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 trialAgain Fanpages,....... much thanks,

B.

I like questions like this that take a bit of thinking & effort, rather than copy'n'pasting "standard" responses.

Hope to see you in another question soon :)

http://www.experts-exchange.com/questions/28704811/Changing-Formats-with-Macro.html

B.

I have just posted the following text in your other thread to draw your attention to a potential issue (that may well require resolution before you deploy this workbook in your user community):

---

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

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,

to:

=dblDateAdd(IF(C6="Minute"

---

Please advise if you wish to discuss this further (here).

Thank you.

Revised formula for cell [C9]...

Please change (the original formula):

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

or (the first revision mentioned above):

=dblDateAdd(IF(C6="Minute"

to:

=dblDateAdd(IF(C6="Minute"

Thank you.

An updated workbook is attached (based on my previous attachment).

Q-28703272c.xlsm

I substituted the new formula and will test it now. At first glance.... it looks like it works well.

Thanks for the follow up. I remember what a Senior Executive once told me, "90% of Leadership is follow up!".

B.

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

Are you opposed to using a Visual Basic for Applications [VBA] code routine to address part of your requirements?

For example, with the code below (& within the attached workbook), I placed the following formula within cell [C9]:

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

This formula returns a date (& time) as expected, I think. Please can you confirm?

For cell [C10], is this just simply locating the "Frequency Measure" (cell [C7]) in the range [B17:B23], then multiplying the corresponding numeric value in column [A] with the value of "# of intervals" in cell [C5]?

If so, the following formula within cell [C10] will do that:

=IFERROR(INDEX(A17:A23,MAT

The code, placed within a (Public/Global) code module ("basQ_28703272") within the workbook, is as follows:

Open in new window

Q-28703272.xlsm