Complicated working days function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 30 Apr 2008 07:33 GMT

I have a start date in F2 and an end date in G2. I am currently using
this formula for the number of working days, which works fine:
=SUM(INT((WEEKDAY(F2-{2,3,4,5,6})+G2-F2)/7))

What I need to do though is have the formula count the number of
working days from F2 up until today(), but only IF G2 is greater than
today, and still count the number of working days from F2:G2 if G2 is
less than today.

Also, if F2 is before 01/01/08, I need to have the formula calculate
the days as if F2 = 01/01/08

Additionally, I need a formula to put in another column that will
also
count the number of working days from today() through G2, but only if
G2 is greater than today()

and also calculating F2 as starting 01/01/08 if F2 is before 01/01/08

Is this a possibility?

Any help is greatly appreciated.

Reply to this Message

Per Jessen - 30 Apr 2008 08:52 GMT

Hi Chris

First we need a helper cell to hold todays date. I use A1 with this formula:

=Today()

Then we need helper column to calculate if F2 is greater or less
01-01-2008 - in cell J2 put this formula:

=IF(F2<DATE(2008,1,1),DATE(2008,1,1),F2)

I calculate working days in H2 (formula to be entered as one line):

=IF(G2>$A$1,SUM(INT((WEEKDAY(J2-{2,3,4,5,6})+$A$1-J2)/7)),SUM(INT((WEEKDAY(J2-{2,3,4,5,6})+G2-J2)/7)))

In cell I2 enter formula below to calculate working days from today to date
in G2:

=IF(G2>$A$1,SUM(INT((WEEKDAY($A$1-{2,3,4,5,6})+G2-$A$1)/7)))

I think that is al´l you need :-)

Best regards,
Per

>I have a start date in F2 and an end date in G2. I am currently using
> this formula for the number of working days, which works fine:
[quoted text clipped - 18 lines]
>
> Any help is greatly appreciated.

Reply to this Message

Bob Phillips - 30 Apr 2008 09:42 GMT

=SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+MIN(G2,TODAY())-MAX(F2,DATE(2008,1,1)))/7))

and

=MAX(0,SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+TODAY()-MAX(G2,DATE(2008,1,1)))/7)))

but shouldn't you be using NETWORKDAYS?

=NETWORKDAYS(MAX(F2,DATE(2008,1,1)),MIN(G2,TODAY()))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Reply to this Message

Chris - 30 Apr 2008 17:48 GMT

> =SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+MIN(G2,TODAY())-MAX(F2,DATE(2008,1,1)))/7))
>
[quoted text clipped - 37 lines]
>
> - Show quoted text -

Thanks Bob, I will try this. I am not using networkingdays becuase I
don't know if everyone who will read this report has the toolpack.

Reply to this Message

Chris - 30 Apr 2008 17:53 GMT

> =SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+MIN(G2,TODAY())-MAX(F2,DATE(2008,1,1)))/7))
>
[quoted text clipped - 37 lines]
>
> - Show quoted text -

Bob - for some reason I am getting a #name? error on this formula

Reply to this Message

Bob Phillips - 01 May 2008 08:47 GMT

which particular one?

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

On Apr 30, 1:42 am, "Bob Phillips" <bob....@somewhere.com> wrote:

> =SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+MIN(G2,TODAY())-MAX(F2,DATE(2008,1,1)))/7))
>
[quoted text clipped - 42 lines]
>
> - Show quoted text -

Bob - for some reason I am getting a #name? error on this formula

Reply to this Message

Bernd P - 30 Apr 2008 12:37 GMT

Hello,

See my formula for "count of working days" at http://www.sulprobil.com/html/date_formulas.html
and set A1 to
=MAX(F2,"01/01/2008")

Regards,
Bernd

Reply to this Message

Teethless mama - 30 Apr 2008 14:09 GMT

Try this:

=NETWORKDAYS(MAX(F2,DATE(2008,1,1)),MIN(G2,TODAY()))

> I have a start date in F2 and an end date in G2. I am currently using
> this formula for the number of working days, which works fine:
[quoted text clipped - 18 lines]
>
> Any help is greatly appreciated.

Reply to this Message