+ Reply to Thread
Results 1 to 2 of 2

Calculating rental period and cost

Hybrid View

  1. #1
    Registered User
    Join Date
    01-04-2019
    Location
    New York, NY
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Calculating rental period and cost

    Hi everyone.

    I need to calculate the total cost given the number of (week) days the equipment was rented.
    This is the info:

    Start date (A2)
    End date (B2)
    Total days (B3) (=networkdays(A2,B2))

    Rates
    Daily (1-3 days) at $75
    Weekly (4-5 days always equal to 5) at $375
    Monthly (= 22 days) at $1,400

    After some help from Google, I found the formula below works to give me the total $.
    =$D$1*MOD($B$3,7)*(MOD($B$3,7) < 5)+$D$2*((MOD($B$3,7) > 4)+INT(MOD($B$3,22)/7))+$D$3*INT($B$3/22)

    (the formula above has spaces between the > and <, otherwise, it won' post)
    However, I still need to know the breakdown of how many months, weeks, and days B3 equals to. Anyone has any ideas how I can get this breakdown?

    ? Months
    ? Weeks
    ? Days
    Total = B3


    Thank you!
    Last edited by JustMax; 02-07-2019 at 08:32 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,157

    Re: Calculating rental period and cost

    is this real calendar, days , weeks , months
    you have the start and enddate
    you could use
    https://exceljet.net/excel-functions...tedif-function
    =DATEDIF(start_date,end_date,"m")
    Unit Result
    "Y" Difference in complete years
    "M" Difference in complete months
    "D" Difference in days
    "MD" Difference in days, ignoring months and years
    "YM" Difference in months, ignoring days and years
    "YD" Difference in days, ignoring years

    IF you want the number of DAYS in B3 changed
    How many days in a month ?

    weeks = B3 / 5
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ 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. Calculate rental spanning different seasonal rental rates
    By Orada in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-25-2017, 04:00 PM
  2. [SOLVED] calculating accumulated rental costs
    By jimcuk in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-25-2016, 03:14 PM
  3. Cost of Goods Macro for New Period
    By Adam Schaefer in forum Excel General
    Replies: 0
    Last Post: 12-04-2014, 09:57 PM
  4. Calculating next due date for rental euipment.
    By ripleyroad in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-17-2014, 11:45 AM
  5. Replies: 4
    Last Post: 11-12-2013, 08:39 PM
  6. Calculate Rental Cost With Varying Rates Based On Rental Days
    By jmenh in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-23-2013, 03:17 PM
  7. Calculating total cost from range of cell tally's by cost of item.
    By patrickdjames in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 11-04-2012, 10:27 AM

Tags for this Thread

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