# How to round up parts of a week to whole weeks

1. ## How to round up parts of a week to whole weeks

Excel 2010
Happy New Year to all,
I have updated an example page.
I am trying to help a charity who hire out mobility scooters and wheelchairs but I'm having trouble doing it.
They could be hired from as little as one day but could be out for 6 months or a year
They charge a fixed fee of £5 for each week or part of a week, so if it is 7 days (one week) it is £5 and if it is 8 days it is £10
I deduct the start date from the end date which gves the days and then try to convert that to weeks but it isn't working out too well. I would like 9 days for instance to = 1 week 2 days, but I can only get 1.29.
The next problem is getting the weekly (or part weekly) fee of £5 to work. for nine days it should be £10. Is there a way of rounding up the amount of days to make an extra week.

Thank you

2. ## Re: How to round up parts of a week to whole weeks

Hi nje,

You need the MOD function somewhere to determine how many days till a new week occurs. Something like:

=(7-MOD(D4-C4,7))+(D4-C4)

See if that helps. If not then break the number of weeks into a cell by itself and the number of days into a separate cell/column.

3. ## Re: How to round up parts of a week to whole weeks

See the attached...it might be you are looking for....

4. ## Re: How to round up parts of a week to whole weeks

Thank you both for your responses and help, I will need to look at it a bit more, but my problem seems to be solved
Could I just clarify with you sbehera, Are the formulas in N O & P needed as part of the solution ?
Thanks again

5. ## Re: How to round up parts of a week to whole weeks

no....N O & P are not part of any formula..i just forgot to delete this before uploading..This I used for only formulating the Complex final formula...

Thanks again

7. ## Re: How to round up parts of a week to whole weeks

Hi nje

What if you put a extra column in, see the attached!

8. ## Re: How to round up parts of a week to whole weeks

Hi,

=ROUNDDOWN((D4-C4)/7,0)+IF(MOD(C4-D4,7)>0,1,0)

Please use this formula in column F......one small correction..sory for the Inconvenience caused......

9. ## Re: How to round up parts of a week to whole weeks

Why not use:
=ROUNDUP((D4-C4)/7,0)

Here, C4 is the start date and D4 is the end date

10. ## Re: How to round up parts of a week to whole weeks

I take it then, that there are a few solutions to this problem. I appreciate all your replies, but some of it has gone well over my head.

11. ## Re: How to round up parts of a week to whole weeks

Hi nje

See the attached, updated!

12. ## Re: How to round up parts of a week to whole weeks

Sorry but I'm back again.
There seems to be a problem with the subtraction of the dates. I have attached another copy. If you take a look at it, you will see what I mean when it deducts one date from the other.
Thanks

14. ## Re: How to round up parts of a week to whole weeks

Hi nje

I couldn't see the advantage of putting text in column E, you cant do a lot with it!

15. ## Re: How to round up parts of a week to whole weeks

in E4
=TEXT((D4-C4)/7,"0.0")&" Week(s) & "&(D4-C4)&" Day(s)"

16. ## Re: How to round up parts of a week to whole weeks

in E4
=TEXT((D4-C4)/7,"0.0")&" Week(s) & "&(D4-C4)&" Day(s)"

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1