display a number as weeks and days?

**di22y** · 01-26-2009, 06:41 PM

I have a start date and an end date in my sheet, in another column I have used the NETWORKDAY function to return the difference excluding weekends and holidays. is there a way I can get this number to be displayed as weeks and days, in seperate columns if need be.

I did consider /5 and returning the result with 1 decimal place to a work cell that would not be displayed. Then creating 2 new columns 'days' and 'weeks' so if the the result in the work cell was 6.2 say that would equate to 6weeks 1day.

In the days column I would then use something like =if(workcell=*.2,"1",if(workcell=*.4,"2", and so on (the syntax is probably incorrect here)

and in the weeks column I would return the number rounded down to the whole number giving me the number of week.

Is there a better way?

EDIT:the * is for anynumber as I thought it was used as a wild card. maybe not!

**Fishhooky** · 01-26-2009, 08:41 PM

Hi,

I think you should try using the rounddown function. To find out the number of weeks try the formula.
=ROUNDDOWN(C7/5,0)

Where you replace C7 with the cell outputting the number of days. This will give you the number of weeks. The number of days can then be found from the formula:

=C7-D7*5

Where D7 is the cell containing the above formula.

Hope that helps.

**daddylonglegs** · 01-26-2009, 09:13 PM

With the NETWORKDAYS formula in C1 you could use this formula for weeks

=INT(C1/5)

and for days

=MOD(C1,5)

If you want you could cut out the intermediate step and put your NETWORKDAYS formula directly into the weeks and days, e.g.

=INT(NETWORKDAYS(A1,B1)/5)

and

=MOD(NETWORKDAYS(A1,B1),5)

**di22y** · 01-30-2009, 07:22 PM

Originally Posted by daddylonglegs

With the NETWORKDAYS formula in C1 you could use this formula for weeks

=INT(C1/5)

and for days

=MOD(C1,5)

If you want you could cut out the intermediate step and put your NETWORKDAYS formula directly into the weeks and days, e.g.

=INT(NETWORKDAYS(A1,B1)/5)

and

=MOD(NETWORKDAYS(A1,B1),5)

Spot On

Thank You.