+ Reply to Thread
Results 1 to 12 of 12

Need help - calculating daily, weekly, and monthly totals based two dates and networkdays

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Need help - calculating daily, weekly, and monthly totals based two dates and networkdays

    Only bill for week days during billing cycle (i.e. M-F).
    More than 3 days = 1 week's rental
    More than 3 weeks = 1 Month's Rental

    Currently "Days" in column E are calculated using Networkdays function.
    Based on entering two dates (Rent start, Rent finished), I need to calculate the number of Months, Weeks, and Days then run a formula to calculate the total value to invoice.

    The highlighted cells under columns J,K,L on row 18 are my initial attempts at this calculation. Please Help!!

    I've attached the excel sheet.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    Can you just provide answers to the following so I can get an idea of exactly how it works

    4 days = 1 week 0 days?
    5 days = 1 week 1 day? or 1 week?
    7 days = 2 weeks 1 day? or 1 week?
    14 days?
    23 days?
    If someone has helped you then please add to their Reputation

  3. #3
    Registered User
    Join Date
    07-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    2 days = 2 days
    3 days = 1 week
    4 days = 1 week
    5 days = 1 week
    6 days = 1 week, 1 day
    7 days = 1 week, 2 days
    8-10 days = 2 weeks
    11 days = 2 weeks, 1 day
    12 days = 2 weeks, 2 days
    13-22 days = 1 Month
    23 days = 1 month, 1 day
    etc.

  4. #4
    Registered User
    Join Date
    07-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    Anyone?
    I've created a table from 1-365, but there has to be a formula out there.
    Thanks!

  5. #5
    Registered User
    Join Date
    07-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    Were you able to take a look at my response to your question?
    Thanks in advance.

  6. #6
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    Ive been looking at it, no doubt its possible within a formula its just an awkward one because you have this period of "free days" as such. Its getting the logic of how that works which is causing the issue.

    No doubt it'll be something fairly simple but not got it yet!

  7. #7
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    Add this function and then call it (referncing your Days cell) from the Months cell. ie in J13 put =CalcMonths(E13)
    Please Login or Register  to view this content.
    Can you test that to see if it gives right results? we can then look at the format of output from there.

  8. #8
    Registered User
    Join Date
    07-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    You've gone quite a ways over my head here, but I think I'm following you.
    I added in the function, and the output is now in J13.
    I would guess the next step is to somehow separate these outputs into the appropriate cells within the function code? I'm not real familiar with VBA code, so I'm open to next step suggestions.
    Thanks again.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    It still wasnt right, 6 days was showing as 1 week 2 days.

    I think ive got it now and have amended the function to only return the item you want (either 1-Months, 2-Weeks,3-days)

    so use it as =CalcMonths(E13,1) in the Months column, (E13,2) in the weeks and (E13,3) in days.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    I've got to run into a meeting, will give it a go later in the day.
    Thanks again, I'll let you know if I have any questions.

  11. #11
    Registered User
    Join Date
    07-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    I've updated the function, but have an error.
    When I update the work days to show 49 days, the function produces 2 months and 1 day, when it should show 2 months and 1 week. I'm using the table in the "DaysTable" tab as my reference to check the function.
    Any thoughts?

  12. #12
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Need help - calculating daily, weekly, and monthly totals based two dates and networkd

    where it says
    Please Login or Register  to view this content.
    change it to -9

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. summing daily weekly & monthly totals
    By jrtaylor in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-10-2014, 04:49 PM
  2. Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  3. [SOLVED] How do I group daily totals into weekly/monthly/quarterly totals
    By situationroom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2013, 09:58 AM
  4. Replies: 0
    Last Post: 12-03-2012, 05:17 PM
  5. Calculate Totals weekly and Monthly from daily Figures
    By mfjanoo in forum Excel General
    Replies: 3
    Last Post: 03-05-2012, 03:06 PM
  6. Replies: 1
    Last Post: 07-11-2008, 05:57 AM
  7. [SOLVED] Collecting weekly and monthly totals from daily data
    By Kasper in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-12-2006, 04:10 PM

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