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.

Joe
  • 572
  • 3
  • 6
  • 22

1 Answers1

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

Community
  • 1
Danny '365CSI' Engelman
  • 21,176
  • 7
  • 35
  • 79
  • Would the workflow action 'Update item in Current Item' trigger the Today() function update? – Joe Oct 19 '15 at 15:49
  • Yes, that is why I presumed you have that workflow in the first place – Danny '365CSI' Engelman Oct 19 '15 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. – Joe Oct 19 '15 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. – Danny '365CSI' Engelman Oct 19 '15 at 16:01