Net duration between 2 dates AND times

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?

Find more posts tagged with

Formulas

Gantt View

Enhancement Request

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…