We help IT Professionals succeed at work.

How late

I am looking for a formula to determine what week range the numbers are late by eg, within one week,two weeks etc.
weeks-overdue.xlsb
Comment
Watch Question

Independent IT and Microsoft Specialist
Commented:
The formula you need is to simply divide by 7 (=A2/7) and then decrease the decimal.   The only caveat is that it will round up to the next week only if the (hidden) decimal is .5 or higher otherwise it will round down, i.e. 17 days would be 2 weeks instead of 3.  In order to always round up to the next week you just need to use the ROUNDUP function like so:
=ROUNDUP((A2/7),0)
The zero just tells the function to always round up to the next integer.
Roy CoxGroup Finance Manager

Commented:
Try in B2 and copied down

=INT(A2/7)

This will return only whole numbers of weeks if you don't want to take into account odd days

You could try MROUND for the weeks

=MROUND(A2,7)/7
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That could be as simple as:

=-INT(-A2/7)

Open in new window

Rob HensonFinance Analyst

Commented:
MROUND will round to nearest factor rather than rounding up. Another option for rounding up is CEILING function:

=CEILING(A2,7)/7

Author

Commented:
gentlemen everyone was great,but i will have to do the first come first serve basis.
Thanks everyone
Tarik M. ZwainIndependent IT and Microsoft Specialist

Commented:
Thank you.