Converting decimals of working weeks to days

Hi All,

I am trying to work out the number of weeks and days between 2 dates, The NETWORKINGDAYS function works this out in days nicely. However if I divide the result by 5 to get weeks I get part weeks expressed as decimals rather than days. I know it doesn’t take a genius to work out that .2 of a working week is 1 day, .4 is 2 days, .6 is 3 days and . 8 is 4 days but I would like Excel to show weeks and days as the result.

EG

My Start date is 15/04/2013 (cell A1)
My Finish date is 09/07/2013 (cell B1)

The formula is

=NETWORKDAYS(A1,B1)/5

This gives me 12.4 as the total number of weeks. But how do I get excel to show that as 12 weeks and 2 days.

Hope this makes sense

Dermot

One way

=SUBSTITUTE(TEXT(NETWORKDAYS(A1,B1)/5,"# w\e\ek\s 0/5"),"/5"," Days")

would you like it to show you in two seperate cells, so Weeks in one and Days in another?

Try

=ROUNDDOWN(NETWORKDAYS(A1,B1)/5,0)&" weeks, "&MOD(NETWORKDAYS(A1,B1)/5,ROUNDDOWN(NETWORKDAYS(A1,B1)/5,0))*5&" days"

A big big thank you to jason.b75, amotto11, BenMillar, SteveG, MartinWilson and shg. All worked!