0

I want to make a view of a list to display the upcoming birthdays.
I have a list "Employees":

list

The column "Birthday" is of type date, the other 4 columns all of type "Calculated (calculation based on other columns)", while the output is of type number.
I made a new view "Upcoming Birthdays", which should show me all birthdays within the next 7 days.
The filter of the view looks as follows: filter

But as an error I get: Filter value is not a valid number.
So instead of [Current month] it expects a number, despite the output type of the column "Current month" being a number. The same error I get for [Current day].

If I just type there the number 1 (for the current month January) and 13 for the current day, everything works as expected.

Improve this question

2 Answers 2

Reset to default
0

Step 1 : Delete all the fields except First Name, Last Name and Birthday

Step 2 : Create a "Calculated Field" of Type "Date and Time" and choose "Date Only" for formatting options and call it "Next Birthday" and the add the following formula

=DATE(YEAR(Birthday)+DATEDIF(Birthday,TODAY(),"Y")+1,MONTH(Birthday),DAY(Birthday))

Step 3 : Create another "Calculated Field" called "Days Until Birthday" with the following formula.

=DATEDIF(TODAY(),[Next Birthday],"d")

Step 4 : Create a View and apply filter on "Days Until Birthday" is less than or equal to 7

Improve this answer
3
  • it works today but not after today because the Calculated Column is calculated on item change. So TODAY() will evaluate to 19/1/2015 And tomorrow that calculation will still be 19/1/2015 and not 20/1/2015 unless the item was changed.
    – Danny '365CSI' Engelman
    Commented Jan 19, 2015 at 19:45
  • Your birthday (date of birth) is always in the past, so this will work since the birthday is always in the past. You have the "Next Birthday" to calculate the next birthday. So make sure you enter the actual date of birth and not the next birthday. Obviously I have not tested this for babies born few hours ago :).
    – Karthik Murugesan
    Commented Jan 19, 2015 at 23:26
  • @DannyEngelman: You are right, the list is static and doesn't refresh daily. Is there a better solution?
    – user38595
    Commented Jan 23, 2015 at 15:28
0

You get errors because you are trying to compare Dates with Numbers

You can use [TODAY] in a View Filter

Using Today() in a Calculated Formula has a side effect you do not want here.

Today() works today but not after today because the Calculated Column is calculated on item change. So TODAY() will evaluate to 19/1/2015 And tomorrow that calculation will still be 19/1/2015 and not 20/1/2015 unless the item was changed. .

How to display coming birthdays in a View

You have to transpose the Birthday to this year (or in other words take the Year out of the equation) so you can compare by number of days offset to [Today]

Off the top of my head; haven't checked the formulas

Create a Calculated column ThisYearBirthday:

=DATE( 2015 , MONTH(Birthday) , DAY(Birthday) )

Then use 2 Filters in your View

[ThisYearBirthday]>[Today]

Gets you all birthdays after Today

AND

[Today]+7<[ThisYearBirthday]

Gets you all birthdays in the next 7 days

You spot the problem for january 2016? You have to change your calculated column to '2016'

You can use Christophe his trick for grouping by dayname

Days to next Date

The x days to the next Birthday/Due Date/whatever date is a whole different beast in SharePoint. You can't do it with Calculated Columns. On 2013 you can use CSR technology or you have to somehow push a JavaScript Today calculation in a Calculated Column with the technique explained on www.ViewMaster365.com

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.