selecting first day of the first month of the selected year

[#1]

Hello,

I wonder how to select the first day of the first month of the selected year and the last day of the last month of the year selected, and when selecting a month he should skip this selection. I need to show the number of months of validity of a contract during the year, regardless of the selection of the month.

thank you!

[swuehl]

>and when selecting a month he should skip this selection.

Do you mean skipping selection in month or year / date?

I assume you want to disregard the selection in month. If you need to calculate an aggregate with first day of year selected, last day of year selected and selection in month skipped, you could use set analysis, something like:

=sum({<Date = {'$(=Date(Yearstart(min(Year))))','$(=Date(daystart(Yearend(max(Year)))))'}, Month=, >} Value)

Selecting these two dates only looks a bit strange too me when asking for number of months of validity , so maybe I misunderstood something completely. Would be helpful if you could detail a bit more how you calculate the number of months.

Regards,

Stefan

[#3]

I have a spreadsheet with a provider, date of accreditation and the date of cancellation by selecting the year 2012 for example, the user wants to display the following: (If the cancellation date is null or> = the last day of last month the selected year (12.31.2012), 12.31.2012, but considering the date of cancellation - (if the date of accreditation is <= the first day of first month of the selected year (01/01/2012) 01/01/2012, date of accreditation).

Ex: if (isnull (dtCancelamento) = -1 or dtCancelamento> = 31/12/2012, 31/12/2012, dtCancelamento) - if (dtCredenciamento <= 01/01/2012, 01/01/2012, dtCredenciamento). Need to know how I get on the values ​​31/01/2012 and 01/01/2012 with the selection of the year ignoring the month if it is selected.

Tks.

[swuehl · Accepted solution]

Ok and your date / year / month fields you select in are not connected to the spreadsheet?

Then maybe like

if (isnull (dtCancelamento) = -1 or dtCancelamento> = makedate(YEARFIELD,12,31), makedate(YEARFIELD,12,31), dtCancelamento) - if (dtCredenciamento <= makedate(YEARFIELD,1,1), makedate(YEARFIELD,1,1), dtCredenciamento)

You can also use yearend() and yearstart() functions, and check out the other date and time functions.

Hope this helps,

Stefan

[#5]

Hello Stefan

Thanks!!!!

Juh