Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So in an excel sheet there is a column called "Days this week" which counts:
Monday as 1,
Tuesday as 2,
Wednesday as 3
(only Mon-Fri)
++
And another column called Days This Month that also works as a counter of working days from the first day of the month as 1, second as 2 etc. and want this to automatically be done for each month.
I want to create these fields based on a budget date.
I've heard about "Network" days, but not sure how to get to this.
Any tips anyone?
Ok...and same for Day This Month I guess?
I believe so... I guess test it out and see if it is working or not
It almost works, except not it is resetting the days this month counter:
Can you try this:
If(MonthName(BudgetDate) = Previous(MonthName(BudgetDate)),
If(not Match(WeekDay(BudgetDate), 'Sun', 'Sat') and not Match(BudgetDate, MakeDate(2016, 1, 1), MakeDate(2016, 2, 1)), RangeSum(Peek('Day this month'), 1), RangeSum(Peek('Day this month'), 0)), If(not Match(WeekDay(Date) and not Match(BudgetDate, MakeDate(2016, 1, 1), MakeDate(2016, 2, 1)), 'Sun', 'Sat'), 1, 0)) as [Day this month]
I think this will work...
It is adding 17 twice and continuing the count so that way the max is 21 instead of 22 because of that.
On week tho there is a 0 now and it starts on 2.
It is the Peek that is doing so that it starts on the correct number or how is that? (scroll all the way to the right to see the picture)
that is strange, let me check this at my end
Ok right now for week it is:
If(not Match(WeekDay(BudgetDate), 'sun', 'sat') and not Match(BudgetDate,
MakeDate(2016, 12, 26)), RangeSum(Num(WeekDay(BudgetDate)), 1), 0) as [Day This Week],
If that helps.
My guess is that you are using Dec 26th as the holiday? Wouldn't you want to see the same number repeated? What else do you want to see?
Have you resolved the month part?