Networkdays Returning 2 when start and end date are the same

Nadine_PrusLoughlin8

Hi team,

I've come across an odd ball that I've never seen before with the Networkdays formula.

The formula I started with was:

=IFERROR(NETWORKDAYS(DATEONLY([Created Date]@row), [Date Closed]@row), " ") which returned the expected number of days inclusive of the "Created Date" until there was an entry that was created and closed on the same day. This returned 2 days instead of 1.

As a test I changed the formula to include the -1 to remove the inclusive day and am still getting 1 day instead of 0 for this row, despite the other lines calculating. correctly.=IFERROR(NETWORKDAYS(DATEONLY([Created Date]@row), [Date Closed]@row) - 1, " ")

This is also happening in parallel with a running day formula I have also set up.

=IFERROR(IF(Status@row = "Done", NETWORKDAYS(DATEONLY([Created Date]@row), [Date Closed]@row), TODAY() - [Created Date]@row) - 1, " ")

Can anyone see what is incorrect? I am stumped because it seems to calculate the other rows correctly.

Thank you in advance!

KPH

I had a suspicion that was what it would show.

My second hypothesis is that you are in a time zone where your 1pm is midnight GMT.

If so, the issue has nothing to do with your formula, nor the fact that the project in question started and ended within a day. It is the fact that that project was the only one to start before 1pm.

The system-generated fields have an issue with timezones that is frustrating. The value seems to be stored in UTC and displayed in the local time. But when you try to use them, for example, extract the date only, they return the date based on UTC. Essentially, for you, I belive, any times before 1pm will show as the previous day when you use the date. They will however, show in local time in the system-generated column. So you are almost blind to what is going on.

😔

The workaround is to create your own date; based on the text string in system-generated ones and use that in your formulas. This is the formula, if you want to do this:

=DATE(VALUE(MID([Created date]@row, 7, 2)), VALUE(LEFT([Created date]@row, 2)), VALUE(MID([Created date]@row, 4, 2)))

The risk here is that anyone looking at your sheet in the future may think you've lost your mind making this so complex and revert to the system-generated dates. Plus this is based on the format of the text string not changing.

Another option is to not use the system-generated fields. Instead, you can create your very own Created Date using an automation to record a date when a row is added. The automation dates will be in the local timezone. I think this is the best workaround.

KPH

Can you add a temporary column to your sheet and use the formula =DATEONLY([Created Date]@row)? Then share what it shows for each of your rows?

I have a hypothesis and that will be the quickest way to disprove it.

Nadine_PrusLoughlin8

Done (Date Only Column.

Wow, ok, how is the date only showing the day before?

KPH

I had a suspicion that was what it would show.

My second hypothesis is that you are in a time zone where your 1pm is midnight GMT.

If so, the issue has nothing to do with your formula, nor the fact that the project in question started and ended within a day. It is the fact that that project was the only one to start before 1pm.

The system-generated fields have an issue with timezones that is frustrating. The value seems to be stored in UTC and displayed in the local time. But when you try to use them, for example, extract the date only, they return the date based on UTC. Essentially, for you, I belive, any times before 1pm will show as the previous day when you use the date. They will however, show in local time in the system-generated column. So you are almost blind to what is going on.

😔

The workaround is to create your own date; based on the text string in system-generated ones and use that in your formulas. This is the formula, if you want to do this:

=DATE(VALUE(MID([Created date]@row, 7, 2)), VALUE(LEFT([Created date]@row, 2)), VALUE(MID([Created date]@row, 4, 2)))

The risk here is that anyone looking at your sheet in the future may think you've lost your mind making this so complex and revert to the system-generated dates. Plus this is based on the format of the text string not changing.

Another option is to not use the system-generated fields. Instead, you can create your very own Created Date using an automation to record a date when a row is added. The automation dates will be in the local timezone. I think this is the best workaround.

Nadine_PrusLoughlin8

Thank you very much, I have changed the auto-date column to an automation date change as suggested above. I agree going nuts with the formula might be a bit much for someone to understand later ha ha ha.

I guess that's what we get for living in NZST!

Thanks again.