Datediff formula help!

[Anonymous]

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.

[Anonymous · Accepted solution]

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 · Accepted solution]

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]

@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 · Accepted solution]

@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.