+ Reply to Thread
Results 1 to 4 of 4

Date Formula

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

    Date Formula

    Hello,

    I'm doing a weekly forecast model and need some help coming up with the best formula.
    If my business has revenue in let's say January, and it takes 2 months to collect, what formula can I use to find 2 months later and spread it by the number of weeks in March?

    Second formula, if I pay commission on the 25th of the following month, but sometimes the week does not end on the 25th....what formula can I use to find closest to?

    Third formula, is to forecast payment of subcontractors. If I have expenses in a given month, I need a formula that recognizes the month of the expenses and spreads the expenses by the number of weeks in the current month.

    Thanks so much....I appreciate the help!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-19-2014
    Location
    Dallas, TX
    MS-Off Ver
    Office2013
    Posts
    44

    Re: Date Formula

    Date2 - Date1 = number of days between the dates. e.g., 10/18/2015 - 8/2/2015 = 77, so those two dates are 77 days apart. Divide by 7 and you get 11, the number of weeks that have passed.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Date Formula

    Or perhaps =WEEKNUM(2nd-date)-=WEEKNUM(First-date) this will give you the weeks difference
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Date Formula

    This is interesting excel project! I love to work on such challenge like that.
    I give a first shot in revenue ( formula in row#8)
    Will come back with Material and Contractor soon.
    Attached Files Attached Files
    Quang PT

+ 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. [SOLVED] Formula for converting a date to quarter and leaving blank cell for no date values
    By jayc2111 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-10-2019, 12:57 AM
  2. [SOLVED] Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)
    By tuongtu3 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-24-2015, 03:29 AM
  3. [SOLVED] Formula to compare date (including month and year) from a listed date to today's date
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 05:31 PM
  4. Replies: 2
    Last Post: 11-03-2014, 10:29 AM
  5. [SOLVED] Help in writing formula in excel to produce dates based on start date and end date
    By shoot for moon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2014, 01:00 PM
  6. [SOLVED] Formula assistance needed to sum data based on anniversary date and month date
    By lilsnoop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2012, 11:18 PM
  7. Replies: 1
    Last Post: 04-18-2012, 05:49 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