0

Using a calculated column to output the day number for the last day of the current month. The current formula is:

=IF(LeapYear, WEEKDAY(CONCATENATE(MONTH(TODAY()),"/",CHOOSE(MONTH(TODAY()),31,29,31,30,31,30,31,31,30,31,30,31),"/",YEAR(TODAY()))), WEEKDAY(CONCATENATE(MONTH(TODAY()),"/",CHOOSE(MONTH(TODAY()),31,28,31,30,31,30,31,31,30,31,30,31),"/",YEAR(TODAY()))))

Occasionally this column is returning #Name? and giving me some grief. I have yet to find a pattern to when/why it's returning this error. There is a daily workflow that updates this list item to maintain a this record as a reference for date information regarding the current week, month, etc.

From 10/11 to today, this has happened on 10/13 and 10/16. LeapYear is another calculated column.

Thanks in advance.

Improve this question
0

1 Answer 1

Reset to default
1

Remember: Today() only recalculates when the ListItem (or Formula itself) is updated.
It does not recalculate like in Excel. (and that is why you use a workflow to update the item every day)

And to get the last day of this month it might be easier to just substract 1 from the first day of next month...

=TEXT( DATE( YEAR(Today()) , MONTH(Today())+1 , 1 ) - 1 ,"mm/dd/yyyy")

this is leapyear safe as well

There are other ways of working with todays date: How to use Today and Me in Calculated column

Improve this answer
4
  • Would the workflow action 'Update item in Current Item' trigger the Today() function update? Commented Oct 19, 2015 at 15:49
  • Yes, that is why I presumed you have that workflow in the first place Commented Oct 19, 2015 at 15:50
  • So I'm still at a loss then, I suppose. If the workflow runs daily at midnight, and updates the list item every time, then the Today() function should update as well, and the corresponding formulas referencing Today() would have their values updated as well. However I still erratically see the #Name? error. For now, I've hard keyed this value, and will utilize the alternate you've provided in a new column to watch its version history over time. Commented Oct 19, 2015 at 15:57
  • #Name? implies a missing reference. Get that [LeapYear] out with my simplified formula, that way the problem can only lie in Today(). Also start your Workflow later... programmers hate things starting at 00:00.. who knows what else your server is doing. Commented Oct 19, 2015 at 16:01

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.