Can the Function WORKDAYS display both Date and Time

**DonkeyOte** · 07-21-2010, 03:04 AM

The below utilises NETWORKDAYS function which forms part of the Analysis ToolPak Add-In - pre XL2007 this must be activated via Tools -> AddIns (else #NAME? will result)

=MAX(0,NETWORKDAYS(A1,A2,holidays)-1+MOD(A2,1)-MOD(A1,1)*((WEEKDAY(A1,2)<6)*(COUNTIF(holidays,INT(A1))=0)))
format cell as [hh]:mm

where Holidays is a named range containing a listing of public holidays which are to be excluded

Does that work for you ?

edit: above is based on your assertion that start datetime might occur on a weekend but have assumed end datetime does not

**noyengrayz** · 07-21-2010, 04:10 AM

Originally Posted by DonkeyOte

The below utilises NETWORKDAYS function which forms part of the Analysis ToolPak Add-In - pre XL2007 this must be activated via Tools -> AddIns (else #NAME? will result)

=MAX(0,NETWORKDAYS(A1,A2,holidays)-1+MOD(A2,1)-MOD(A1,1)*((WEEKDAY(A1,2)<6)*(COUNTIF(holidays,INT(A1))=0)))
format cell as [hh]:mm

where Holidays is a named range containing a listing of public holidays which are to be excluded

Does that work for you ?

edit: above is based on your assertion that start datetime might occur on a weekend but have assumed end datetime does not

The formula worked for me.This is exactly what I needed. Thank you very much.

**noyengrayz** · 07-21-2010, 04:11 AM

Kindly considered this forum closed. thanks!

**noyengrayz** · 07-21-2010, 04:50 AM

I have problem with my formula.
This is the scenario:
I have a start date and time and I need to generate automatically the end date and time excluding weekends. Our working hours is 24 hours a day.
I can do it using WORKDAYS but I the time does not coincide.
For example:
start date/time: July 1, 2010 5:00 (A1)
deadline is 1 day
end date/time should be: July 2, 2010 5:00
but what I get is: July 2, 2010 00:00
I'm just using the... =WORKDAY(A1,1)

I also need the formula to work excluding the weekdays.

Thanks in advance.

**DonkeyOte** · 07-21-2010, 04:54 AM

Just add the Time of A1 to the WORKDAY result, eg:

=WORKDAY(A1,1)+MOD(A1,1)

If you need to account for start date on weekends etc per your other thread then adjust the addition of the MOD accordingly.

**noyengrayz** · 07-21-2010, 07:30 PM

Thanks for the help the formula already worked for me.
As for my other thread, the formula on networkdays worked fine except when i have a start date on a weekend and ends on the same date at a later time. The formula gives me 0.00 result.
A35: 7/10/2010 7:33 - start date/time
D35: 7/10/201 16:07 - end date/time
Using =(MAX(0,NETWORKDAYS(A35,D35)-1+MOD(D35,1)-MOD(A35,1)*((WEEKDAY(A35,2)<6))))*24)
Result: 0.00

Also, can you explain how the formula works in the simplest way? I understand the networkdays but the Mod function*weekday...they're still vague to me.
Thanks in advance

**DonkeyOte** · 07-22-2010, 01:44 AM

These questions should really have been asked on your other thread...

Originally Posted by noyengrayz

the formula on networkdays worked fine except when i have a start date on a weekend and ends on the same date at a later time. The formula gives me 0.00 result.

If the start & end date is a non-working day why would the working hours result be anything other than 0 ?

Originally Posted by noyengrayz

I understand the networkdays but the Mod function*weekday...they're still vague to me.

The

MOD(A35,1)*(WEEKDAY(A35,2)<6)

is used to determine whether or not the StartDate Time is relevant to the calculation - we/I had deemed this irrelevant based on your other post where the start date was on a non working day, eg:

Start: Sat 10:00 AM
End: Mon 2:00 PM

Result would be 14 hours - ie all Time up until Monday 0:00 is disregarded.

In terms of the mechanics...

The Boolean resulting from the WEEKDAY test will be coerced via the multiplication - so you end up with

TIME * FALSE -> TIME * 0 -> 0

or

TIME * TRUE -> TIME * 1 -> TIME

**noyengrayz** · 07-22-2010, 07:44 PM

[QUOTE=DonkeyOte;2348638]

If the start & end date is a non-working day why would the working hours result be anything other than 0 ?

I'm sorry, the situation I placed is quite confusing.
Actually, what happens is, we have 2 job level in our group. The indirect and the direct. The indirect has non-working weekends. While the direct have working weekends. But we don't want to account the working weekends even for the direct although in actual they are working and can start a task on a weekend and ends also in a weekend.
Is this possible in excel.
By the way, my apology for posting here and not in my other thread.
Thanks also for the explanation you provided.

**DonkeyOte** · 07-23-2010, 01:54 AM

Originally Posted by noyengrayz

The indirect has non-working weekends. While the direct have working weekends. But we don't want to account the working weekends even for the direct although in actual they are working and can start a task on a weekend and ends also in a weekend.

So again, I would ask why the result should be anything other than 0 where start & end are weekend ?
As far as I can tell this question has not been answered.

**noyengrayz** · 07-23-2010, 07:41 PM

Originally Posted by DonkeyOte

So again, I would ask why the result should be anything other than 0 where start & end are weekend ?
As far as I can tell this question has not been answered.

I'm expecting results such as number hours or minutes only.
For example:
7/10/2010 8:00 AM - start date/time
7/10/2010 11:30 AM - end date/time
Result should be 3.5 hours, right? but what i'm getting is 0.

Such situation is not true during weekdays, where I'm getting the right results.
Thanks again.