Forum Discussion
Datediff formula help!
Hi Anonymous If I understood you somewhat correct this is what I would do:
For the COMPLETED PROJECT DAYS: If you only want to display values for completed projects and have blank cells for ongoing projects do the following:
IF(ISBLANK(TableX[Finished]);" ";DATEDIFF(TableX[Started];TableX[Finished];DAY))
This ignores ongiong projects.
For the ONGOING DAYS column: if you want the number of days for ongiong projects and ignore finished use the following
IF(ISBLANK(TableX[Finished]);DATEDIFF(TableX[Started];Today();DAY);" ")
My own recommendation:
personally I would have went with a column for the number of days for every project (finished and ongoing) and then have a column to indicated if the project is finished or ongoing. In the order I've mentioned the columns would look like this
IF(ISBLANK(TableX[Finished]);DATEDIFF(TableX[Started];Today();DAY);DATEDIFF(TableX[Started];TableX[Finished];DAY))
IF(ISBLANK(TableX[Finished]);"Ongoing";"Finished")
This'll also make it easier when displaying a slicer for viewing projects in the report.
Anonymous as you hinted it it's very similar in fact it's the same function you need to use as I put in the last part of my other message. The following
IF(ISBLANK(TableX[Finished]);DATEDIFF(TableX[Started];Today();DAY);DATEDIFF(TableX[Started];TableX[Finished];DAY))
The function evaluates if the cell in the stop date column is empty (=Project is ongoing) or not and uses the appropriate calculation based on this.
Hope it helps.
Anonymous
Thanx alot!
I have another question if you can answear me.
Which is almost the same.
Here I have a project ID when a projects starts. Then some projecets stops. Here I have made a datediff duration, which takes todays date and shows the duration of the project.
So Can you help me with setting up a formula that says.
If project ID has a start and stop time give me this, if it only has a start date give me output duration of the project today.
I want to replace project id 1 = from 22 days to 5 days
And I want to replace project id 2 = from 21 days to 9 days. But the rest is correct since it doesnt have any stop time yet. It really depends when a project stops.
In the same coulmn
Hope this is clear.
Anonymous as you hinted it it's very similar in fact it's the same function you need to use as I put in the last part of my other message. The following
IF(ISBLANK(TableX[Finished]);DATEDIFF(TableX[Started];Today();DAY);DATEDIFF(TableX[Started];TableX[Finished];DAY))
The function evaluates if the cell in the stop date column is empty (=Project is ongoing) or not and uses the appropriate calculation based on this.
Hope it helps.