Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 23

Thread: Date Formula

  1. #1

    Date Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Sir,

    I am seeking data formula/function that could calculate the number of days from the expected date to actual date.
    I can come up with formula to calculate the number of dates between real dates and expected dates. Here is the example parameters:

    Range Definition: (days of month)
    Beg 1-10
    mid 11-20
    End 21-30

    Expacted date for submission Actual Date Formula
    21-Dec-14 21-Dec-14 0
    21-Dec-14 18-Dec-14 -3
    21-Dec-14 27-Dec-14 6
    Beg Dec 14 9-Dec-14 #VALUE!
    End Dec 14 18-Dec-14 #VALUE!
    Mid Dec 14 27-Dec-14 #VALUE!
    How can i calculate date difference in case of date relate to particular range? Any one can help please.

    Regards

    Shehbaz

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    What should the result for those 3 be then?


  3. #3
    I want a formula to calculate the difference between real data and expected date. what will be the formula in such particular case when date is based on some range assumption?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    I understand, but each range has about 10 days... do you want to subtract the bottom of the range, the top of the range, which date to use?


  5. #5
    This is the actual question what i want i.e. top,bottom,average or other date value which will be put in such formula?

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Show what your expected results should be in those 3 cells.


  7. #7
    I would need to calculate all possible ranges depending on the actual date, as it will differ in most of the cases.
    That is, if for example, I have expected date is Mid Feb and actual date is 23 Feb, it should come out positive value of +3, as the range value for Mid is 11 to 20
    - for example If expected date is Mid Feb and actual date is 15 feb, the value should be 0, as it was within the range of Mid
    - Expected date is Mid Feb and the actual date is 09 Feb, the value should be -2, as range starts at 11.

    I hope you will be clear now now give me the formula

    Regards

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    I think this takes far too much effort. What happens when a month has 31 days or 28/29, using a default end of 30 will return a wrong result.

  9. #9
    Sorry, the end rang is 21-31 please correct it.

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    Correct what, I said it is too complex because months don't all have 31 days. What happens in Feb, or Sep, Apr, Jun and Nov?

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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