NETWORKDAYS - EXCEL 1

[qlan]

Hi,

I have this formulas: IF(NETWORKDAYS(NOW(),Info!D34) < 5,0.2,0)

Currently, my Info!D34 is 7/14/2006 10:00:00 AM.

I would like to do something like if the time is after 5:00PM and before 11:59PM, then it would be the next day. In this case, my formulas would be something like < 6 instead of <5. Any help would greatly be appreciated.

 

[firefytr]

Hi there,

Maybe you can replace this ..

Info!D34

.. with this ..

(Info!D34+((Info!D34-INT(Info!D34))>0.7083))

That basically takes the time portion of the date, checks if it's after 5:00 pm (the decimal), if so it returns TRUE, if not it returns FALSE. Put that in a mathematical operation and it coerces TRUE to a 1 value and FALSE to a 0 value; hence adding it to the original value, one day later or the same.

HTH

-----------
Regards,
Zack Barresse

 

[AnotherHiggins]

Real quick suggestion - instead of 0.7083 use the actual time
[tab](Info!D34+((Info!D34-INT(Info!D34))>"17:00"))

0.7083 actually converts to about 16:59:57 - 5 PM has repeating 3s at the end.

qlan: For more info on how Excel deals with dates and times, see faq68-5827, "Why do Dates and Times seem to be so much trouble?".

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

Good point John. Thanks.

-----------
Regards,
Zack Barresse

 

[qlan]

Hi,

I have this formula:

=IF(NETWORKDAYS(TODAY(),Info!D34) < 5,0.2,0)

Currently, TODAY() is July 10 and my Info!D34 is July 14, which make my NETWORKDAYS less then 5. I am expecting that there should be a 20% charge. However, the result I got if 0%. I also tried, =IF(NETWORKDAYS(NOW(),(Info!D34+((Info!D34-INT(Info!D34))>"17:00"))
) < 5,0.2,0), I got the same result. Any suggestion? Thanks so much in advance

 

[TonyJollans]

Hmmm..., couple of problems here ...

NETWORKDAYS is inclusive so ...
[blue][tt] NETWORKDAYS("10/07/2006","14/07/2006")[/tt][/blue] will return 5.

NETWORKDAYS doesn't look at the times, just the dates. You will need to handle the time separately but I'm not quite sure what you want to check - the time now or the time in Info!D34. I think the easiest would be to roll forward the times you have by 7 hours, perhaps ...
[blue][tt] NETWORKDAYS(NOW()+"7:00",Info!D34)[/tt][/blue]
or ...
[blue][tt] NETWORKDAYS(NOW(),Info!D34+"7:00")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[qlan]

I want to check that
if NOW() is 07/11/06 and the time is anytime after 5:00PM, then it would consider NOW() as 07/12/06. As you mentioned, NETWORKDAYS doesn't look at the times; is there another function I can use so my formula would look at both (Dates and times). Thanks so much

 

[AnotherHiggins]

I strongly suggest you take a minute to read the FAQ I linked to in my last post. Once you understand how Excel deals with dates and times, it all becomes easy.

->if NOW() is 07/11/06 and the time is anytime after 5:00PM, then it would consider NOW() as 07/12/06.
=IF(NOW()-INT(NOW())<"17:00",TODAY()+1,TODAY())



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[TonyJollans]

Hi qlan,

If you add 7 hours to NOW() then the resulting date will be unchanged if the time is before 5.00 pm and the next day if it is after 5pm. So this is the variation I think you want ..
[blue][tt] =IF(NETWORKDAYS(NOW()+"7:00",Info!D34)<5,0.2,0)[/tt][/blue]

John,

I prefer [blue][tt]MOD(NOW(),1)[/tt][/blue] to [blue][tt]NOW()-INT(NOW())[/tt][/blue] but I really think you're making it all too complex with the [blue][tt]IF[/tt][/blue] in the first place Using [blue][tt]NOW()+"7:00"[/tt][/blue] doesn't require any knowledge of how dates and times are held.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[qlan]

Thanks so much! it works. I used IF(NETWORKDAYS(NOW()+"7:00",Info!D34)<5,0.2,0)