Forum Discussion
Inconsistent Values from PWA Custom Field Formula
Hello community.
Greetings from first time poster.
I created the following formula (“FY Project Completion”) to assign a Financial Year value based on Project Finish Date (“Finish”): if the project ends on or before June 30, 2024, Financial year will be FY24, if the project end date falls between Jul 1, 2024 and June 30, 2025, the Financial Year will be FY25, and so on.
IIf([Finish] >= CDate(#7/1/2024#) And [Finish] <= CDate(#6/30/2025#), "FY25", IIf([Finish] >= CDate(#7/1/2025#) And [Finish] <= CDate(#6/30/2026#), "FY26", IIf([Finish] >= CDate(#7/1/2026#) And [Finish] <= CDate(#6/30/2027#), "FY27", IIf([Finish] >= CDate(#7/1/2027#) And [Finish] <= CDate(#6/30/2028#), "FY28", "FY24"))))
Surprisingly, the formula gives inconsistent FY values, even for projects with same Finish Date: sometimes it is blank, other times it assigns different FY values.
I am attaching a representative listing of about 40 projects, 23 of which have wrong FY value.
I will very much appreciate it if somebody can please guide me where I need to make change in the formula to get correct values?
| Dummy Project Name | Start | Finish | FY Project Completion |
| Project in Row 2 | 11/13/2018 | 4/30/2021 | FY24 |
| Project in Row 3 | 3/24/2023 | 3/24/2023 | FY25 |
| Project in Row 4 | 12/14/2020 | 3/29/2023 | |
| Project in Row 5 | 3/2/2020 | 7/31/2023 | FY24 |
| Project in Row 6 | 10/25/2023 | 11/22/2023 | FY25 |
| Project in Row 7 | 1/3/2022 | 12/31/2023 | FY25 |
| Project in Row 8 | 4/6/2020 | 2/9/2024 | FY24 |
| Project in Row 9 | 9/7/2023 | 3/1/2024 | FY25 |
| Project in Row 10 | 1/3/2023 | 3/29/2024 | FY24 |
| Project in Row 11 | 4/1/2024 | 4/1/2024 | FY24 |
| Project in Row 12 | 4/1/2024 | 4/1/2024 | FY24 |
| Project in Row 13 | 4/8/2024 | 4/8/2024 | FY24 |
| Project in Row 14 | 4/8/2024 | 4/8/2024 | FY24 |
| Project in Row 15 | 3/29/2024 | 5/9/2024 | FY24 |
| Project in Row 16 | 5/16/2024 | 5/16/2024 | FY25 |
| Project in Row 17 | 11/14/2023 | 5/28/2024 | FY24 |
| Project in Row 18 | 6/11/2024 | 6/11/2024 | FY25 |
| Project in Row 19 | 7/26/2023 | 6/28/2024 | FY24 |
| Project in Row 20 | 1/3/2023 | 6/28/2024 | FY25 |
| Project in Row 21 | 1/2/2024 | 6/28/2024 | FY25 |
| Project in Row 22 | 1/3/2023 | 6/28/2024 | FY25 |
| Project in Row 23 | 1/3/2023 | 6/28/2024 | FY24 |
| Project in Row 24 | 1/3/2023 | 6/28/2024 | FY24 |
| Project in Row 25 | 1/3/2023 | 6/28/2024 | FY26 |
| Project in Row 26 | 1/3/2023 | 6/28/2024 | FY24 |
| Project in Row 27 | 7/27/2023 | 6/28/2024 | FY25 |
| Project in Row 28 | 1/24/2024 | 6/28/2024 | FY25 |
| Project in Row 29 | 1/3/2023 | 6/28/2024 | FY24 |
| Project in Row 30 | 1/3/2023 | 6/28/2024 | FY26 |
| Project in Row 31 | 1/3/2023 | 6/28/2024 | FY24 |
| Project in Row 56 | 1/3/2023 | 6/28/2024 | |
| Project in Row 57 | 1/3/2023 | 6/28/2024 | FY26 |
| Project in Row 58 | 1/3/2023 | 6/28/2024 | FY26 |
| Project in Row 59 | 5/24/2023 | 6/28/2024 | FY24 |
| Project in Row 60 | 1/17/2023 | 6/28/2024 | FY26 |
| Project in Row 61 | 1/3/2023 | 6/28/2024 | FY26 |
| Project in Row 62 | 9/1/2022 | 6/28/2024 | FY26 |
| Project in Row 63 | 1/23/2023 | 6/28/2024 | |
| Project in Row 64 | 7/3/2023 | 6/28/2024 | |
| Project in Row 65 | 10/28/2020 | 6/28/2024 | FY25 |
Hi Bhatia,
Your formula turned out fine in my Project Desktop environment.
Try copying and pasting the formula into another custom field.
You can also try this other similar formula (copy and paste):
IIf([Finish]<cdate("7/1/24"),"FY24",IIf(month([Finish])>6,"FY" & right(year([Finish]),2)+1,"FY" & right(year([Finish]),2)))
I look forward to your comments.
Ignacio
Hi Bhatia,
Your formula turned out fine in my Project Desktop environment.
Try copying and pasting the formula into another custom field.
You can also try this other similar formula (copy and paste):
IIf([Finish]<cdate("7/1/24"),"FY24",IIf(month([Finish])>6,"FY" & right(year([Finish]),2)+1,"FY" & right(year([Finish]),2)))
I look forward to your comments.
Ignacio
Ignacio_Martin,
I like your approach, instead of a lengthy formula, you created an algorithm that is short, concise and will work for several years.😀
John
Thanks. I checked this formula in my Lab environment and it gives correct results. THANK YOU.
In my Production environment, re-published some projects and for those I got correct results. BUT there are some projects where the formula still shows blank value. Since I cannot find any explanation for that, I think I will ask my organization if we can involve MSFT through s support ticket.
Bhatia,
I can't explain why some values are showing up as blank but I can give you some insight about your formula.
Remember that dates in Project are not just date values but also include the time, regardless of how a date is displayed in a view. So 6/1/24 is actually 6/1/24 8:00 AM if a start date and 6/1/24 5:00 PM if a finish date, assuming the Standard calendar. However, when the CDate function is applied to a date such as 6/1/24, it produces 6/1/24 12:00 AM. Attempting to use a "greater than or equal to" or "less than or equal to" operator in a custom field formula needs to take the time factor into account.
Hope this helps.
John
Hi John-project:
Thanks for suggesting the date nuance. I will keep a note of that. In this case (see above example from today) when the date is 6/30/24, I get two different FY values.
Bhatia,
Sorry but I don't see any updates to your example. The latest finish date I see is 6/28/24 for several rows. Did you try Ignacio's custom field formula? It works great for me.
John
For some reasons I am not seeing my replies from yesterday and today. I think I will create another thread and post my reply there...
Bhatia,
Why start a new thread, your latest reply is here. Are you looking in the "most recent" category of postings?
John
