DSXchange

Hello,

I have requirement where I need to find the difference between 2 days and check if it Week1, Week2, Week3 , Week4 or month1 or Month2 Etc.

I have calculated the difference by applying below mentioned function i..e.
Number =DaysSinceFromDate(D1,'2010-01-01')

My output should be as folows:-

Number Output should be
6 Week1
12 Week2
24 Week2
33 Month1 and so one.

We may have max 1 Year data i.e. 365 days. so max. the output value can be Month 12.

We have to make sure that leap year is also considered.

Please let me know is there any functions or I need to write a If else statement.

Regards
Neeraj

Please let me know

Why a duplicate post?
One post is enough here to get your problem solved

I don't understand your logic.
Please explain clearly.
How week2 comes both for 2nd and 3rd records in your sample?
And how you are deciding Week or Month?

i don't understand your requirement,
But one thing i am sure is, you need to write your own if..else construct.
There is no chance to get n built function for your requirement.

A simple If Then Else could work here.

If NoOfDays < 30 Then Ceil(NoOfDays/7) : "Week"
Else Ceil(NoOfDays) : "Month"

Also how come 33 is coming in Month1? If it has to then subtract 1 from it and you have your output.

Sorry,

It was a typo error

Just to elaborate

1-7 Week1
8-14 Week2
15-21 Week3
22-28 Week4
29-31 Week 5
32-60 2 Month
61-90 3 months



I hope I am clear..

Regards
Neeraj

As told,an if ... else block, you can construct for this.
it looks like <31, you can use ceil() function or getting week no.
For month, explicitly you need to check.

pandeesh wrote:Why a duplicate post?
One post is enough here to get your problem solved

\

I am sorry for duplicate post.

Can you please share the link where I can find the information.

Why you are bothering to write an if ..else construct yourself ?
I don't see any complexity in that .
If you still want the complete solution, I will post,..

Finally, here is the sample:

Sure seems like a Mod() might come in handy here...

Craig,
For calculating month number, i don't think Mod() will give the handy solution.
Please share, if you have any thoughts

If there is a consistent number of days they consider to be a 'month' then a mod() is a simple solution. The example is a little... odd... to me as the first 31 days are considered a month, then 28 then 29 and then who knows.

It's not clear enough yet. I think if you are clear on the business rules then you can state a more clear requirement.

Do you need to determine the week number, the month number, both week and month numbers, or does it depend on something?

It looks like you have made assumptions that the number only falls into week 5 if the number is in range 29-31. And also that number only falls into month 2 starting with number = 32. What about leap years?

Calendars don't always work like that. Week and month definitions can vary based on business rules. For example, I have seen calendars with week 53 that spans into the first few days of January the following year.

Only some months have 31 days. You do not appear to be comparing a specific calendar date with a real live calendar, but rather you are taking the number of days between two dates and then trying to match it up with a generic, artificial definition of weeks and months?

Weeks is easy. And if you're only interested in 01/01/yyyy as the base date, then 31 is a reasonably day count figure to use.

Months, on the other hand, require you to extract the day number and month number, and compare these. Pertinent functions are MonthDayFromDate() and MonthFromDate().