+ Reply to Thread
Results 1 to 16 of 16

How to round up parts of a week to whole weeks

  1. #1
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    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
    Attached Files Attached Files
    Last edited by nje; 01-07-2013 at 02:00 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,536

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    08-26-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

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

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

  4. #4
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    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. #5
    Registered User
    Join Date
    08-26-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    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...

  6. #6
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

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

    Thanks again

  7. #7
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    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!
    Attached Files Attached Files
    Last edited by Kevin UK; 01-07-2013 at 02:29 PM. Reason: Copy formulas down column
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  8. #8
    Registered User
    Join Date
    08-26-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    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. #9
    Forum Contributor
    Join Date
    01-28-2008
    MS-Off Ver
    Excel 2016
    Posts
    149

    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. #10
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    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.
    Thank you for your assitance
    Last edited by nje; 01-07-2013 at 02:43 PM.

  11. #11
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

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

    Hi nje

    See the attached, updated!
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    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
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

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

    Kevin, Thanks, I've just looked at your latest version and it's very good

  14. #14
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

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

    Hi nje

    Glad your sorted and thanks for the feed back.
    I couldn't see the advantage of putting text in column E, you cant do a lot with it!

  15. #15
    Registered User
    Join Date
    12-26-2012
    Location
    Meerut
    MS-Off Ver
    Excel 2007
    Posts
    86

    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. #16
    Registered User
    Join Date
    12-26-2012
    Location
    Meerut
    MS-Off Ver
    Excel 2007
    Posts
    86

    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)"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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