Question

Locked

Excel formula for calculating time

By matamin55 ·
Can anyone help..
In cell A2 i have Received Date/Time, cell B2 Response Date/Time, work start at 9am (C2)and ends at 8pm (D2).

Received Date/Time 31/12/2009 9:00 AM
Response Date/Time 01/01/2010 10:00 AM
Work Day Start Time 9am
Work Day End Time 8pm
Working hours to resolve 12:00:00

i'm using =(NETWORKDAYS(A2;B2)-1)*(D$2-C$2)+IF(NETWORKDAYS(B2;B2);MEDIAN(MOD(B2;1)$2;C$2)$2)-MEDIAN(NETWORKDAYS(A2;A2)*MOD(A2;1)$2;C$2) formula to get the resolve hours within the workdays (Mon - Fri), but it skipped the weekend.

if the request comes after working hours, i.e 8:30pm and solved next day at 9:30am the calculation should start at 9:00am next day and resolve time is 00:30:00(minus after office hours)

How to calculate the incoming case and resolve time on weekend as well because my team works 7 days a week.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

RE:

by NexS In reply to Excel formula for calcula ...

I don't know about everyone else, but I find it hard to follow your question and know what it is that you want.

Perhaps I'm a bit 'simple' but would you be able to tell us exactly what you need your worksheet to do?

Collapse -

Excel formula for calculating time

by matamin55 In reply to RE:

I need to calculate total time needed to response to the request. The calculation should only consider within the working hours.

if the request comes after working hours, i.e 8:30pm and solved next day at 9:30am the calculation should start at 9:00am next day and resolve time is 00:30:00(minus after office hours)

The =(NETWORKDAYS(A2;B2)-1)*(D$2-C$2)+IF(NETWORKDAYS(B2;B2);MEDIAN(MOD(B2;1)$2;C$2)$2)-MEDIAN(NETWORKDAYS(A2;A2)*MOD(A2;1)$2;C$2) formula only calculate workdays and skip the weekend (Sat & Sun).

How to calculate the incoming case and resolve time on weekend as well because my team works 7 days a week.


thanks

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums