Net duration between 2 dates AND times

valentink

Hi folks, 

Ok, this is a tricky one:

I'm trying to set something that was requested by many people on this community forum - a NETWORKDAYS that can account for TIME as well. In other words, the amount of working days/hours between to periods (that accounts for.

There is this amazing site: https://www.exceltactics.com/calculate-net-work-hours-using-networkdays/

=IF(OR($End_Time<$Start_Time,End_Date<Start_Date),0,

        (NETWORKDAYS(Start_Date,End_Date,HolidayList)

        -(NETWORKDAYS(Start_Date,Start_Date,HolidayList)

        *IF(MOD(Start_Date,1)>$End_Time,1,

                (MAX($Start_Time,MOD(Start_Date,1))-$Start_Time)

                /($End_Time-$Start_Time)))

        -(NETWORKDAYS(End_Date,End_Date,HolidayList)

        *IF(MOD(End_Date,1)<$Start_Time,1,

                ($End_Time-MIN($End_Time,MOD(End_Date,1)))

                /($End_Time-$Start_Time))))

        *($End_Time-$Start_Time)*24)

 Problem 1) There is no MOD fonctions in smartsheet, but there is a workaround when you think about it (@Craig, you are my star on this forum, it's always good stuff) that can be found here : https://community.smartsheet.com/discussion/can-smartsheet-perform-mod-function

So (this is from Craig) :

 =MOD(F3,64)*4    = 160 

can be converted to this

=(F3 - (64 * INT(F3/64))) * 4

The Modulus MOD(A,N) is:

=A - (N * INT(A/N))

and then in your example you multiply by 4

but...

Problem 2) How do I get around the START_DATE / END_DATE that, in excel, includes the DATE AND the TIME? As far as I know, there is no way to do that in smartsheet. You need to columns for each (one being the date and one being the time). So is there a NETWORKHOURS type of option we can write?

Anyone? Anyone? Bueller?

Mike Wilday

This is a tricky one. Time is not managed well in Smartsheet as compared to other spreadsheet applications. Smartsheet functions primarily off the server time - wherever that is - so sometimes data is submitted after 8:00 pm pacific time, but that registers next day in eastern and then days are adjusted to account for the server time. There are a lot of complaints about this in the forums but time is not something that has been very thoughtfully built out in smartsheets yet. You'd be better off focusing on Dates than actual times. Sorry for the sad news. 

Chris McKay

Hi valentink,

I know this was for Craig, but I'll chime in too.

What Mike has posted is 100% correct. But I'll go one step further. Saying that the Smartsheet app not handle time well is an understatement. The application views all time as being based in the UTC timezone. So even though your local timezone is displayed in system date columns, the underlying server time is always UTC.

If like me, you are well outside UTC (I'm UTC +10) that means you can have changes made on one day, (and displayed as such), but the underlying time may b recorded as yesterday. I often have to confirm with users when they made a change. Pretty embarrassing for a project management solution when you can't rely in times right?

The Product Team have decided to turn off time functionality until they work out how to approach the problem at hand. Sadly (and disappointingly), that was roughly 2 years ago.

Sadly, I don't believe there is any way to achieve what you'd like to do inside Smartsheet without getting seriously creative with the API and developing a workflow outside. But then what's the point of using Smartsheet?

Kind regards,

Chris McKay