Forum Discussion
Datediff formula help!
Can someone help me with setting up a datediff formula or some other that can be used in this case. only have this
I have a column which have a start date when a project starts. You register that with a app ( start date) .
I want to know the current duration of an ongoing project. It depends when it stops til someone goes to the app and punches in a stop time. If there is no stop just give me ongoing days.
If a project starts at 01.01.19 and goes for until 5.01.19.
And then I want to know the total duration of a completed project in a new column.
So two column one ongoing and one completed project days. I only have start date. And stop date.
Start date: Ongoing: Stop Date. Completed project days
01.01.19 1
2
3
4 05.01.19 4
Or setting ongoing to just count days 1. Instead of 1, 2, 3 ,4 . Just see what is possible or not. Both can be used!
Start date: Ongoing: Stop Date. Completed project days
01.01.19 1
1
1
1 05.01.19 4
want to create. ongoing and complete.
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.
3 Replies
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
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.