Excel: Calculating working hours between two dates

microsoft-excel microsoft-excel-2003 worksheet-function

6,680

whole working days = NETWORKDAYS - 2

time worked on first day = MAX(0, endofday - MAX(starttime, startofday))

time worked on last day = MAX(0, MIN(entime, endofday) - startofday)

total work days = whole working days + INT((time worked on first day + time worked on last day) / (endofday - startofday))

extra time (after total days) = MOD(time worked on first day + time worked on last day, endofday - startofday)

6,680

AliRıza Adıyahşi

Updated on September 17, 2022

Comments

AliRıza Adıyahşi almost 2 years
I want to set checkbox column in gridview. Following codes do that:
```
gridSettings.PreRender = (sender, e) =>
{
    MVCxGridView gridView = sender as MVCxGridView;
    for (int i = 0; i < gridView.VisibleRowCount; i++)
    {
        gridView.Selection.SetSelection(i, (bool)gridView.GetRowValues(i, "IsAssigned"));
    }
};
```
But I have about 1 million records, so grid view loading time is too long (more than 2 minutes). Because devexpress, loop all records, page by page to find selected rows (my collection is IQueryable type). If I dont try to set this column, devexpress use IQueryable list, so it fetchs only one paged data.

What can I do to loop only visible rows?
- DMA57361 over 13 years
  
  Have you tried using Tools -> Formula Auditing -> Evaluate Formula on the formula that is failing?
- DMA57361 over 13 years
  
  HOUR(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1))) concerns me; Excel cannot interpret negative values as date/time values - I suspect this is the problem because the two times given (12:00 -> 11:30) result in a negative value of 30mins (-0.0208333..).
- Murray Furtado over 13 years
  
  @DMA57361 yes it's the negative result that is the problem, but I'm at a loss for how to solve that.
- DMA57361 over 13 years
  
  Can you delay or remove the call to HOURS()? Dates are just stored as numbers under the hood (with a step of 1 being a day, so 1/24 is an hour, etc). If you keep them as numbers you can simply add them up, and then manually format the resulting field as date/time.
- RSMoser over 13 years
  
  Could you use the absolute function to give you a psoitve number?
- Yahya about 10 years
  
  Answered here devexpress.com/Support/Center/Question/Details/Q291307
LaC about 13 years

@torbengb: wait, what comment?
Murray Furtado about 13 years

Oh, it has been moderated in the meantime. Please disregard!