+ Reply to Thread
Results 1 to 6 of 6

Help to adjust date formula

  1. #1
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    Help to adjust date formula

    Thanks for previous help.
    Everything works in principle but have another glitch. The formula adds up the dates ok but I somehow need to take account of ...
    Example...
    1/10/13 to 14/10/13 is 14 days but the formula adds it as 13 because I am doing a subtraction, I managed to get around that by adding 1 on to each date calculation, however I need to get the cell M11 to show the same total as E18.

    Can the formula in M11 be adjusted to compensate?
    I have attached the file.

    thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Help to adjust date formula

    Why don't you just use

    =SUM(E12:E17)

    in M11?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    Re: Help to adjust date formula

    Because there is a roll back on the dates of 180 days in E6 so all dates are not always included. So the current formula adds up the days within a 180 day period. If that makes sense.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Help to adjust date formula

    You can just add +1 to the M8 formula too

    =SUM(IF(D12:D17>M8,D12:D17-IF(C12:C17<M8,M8,C12:C17)+1))

    confirmed with CTRL+SHIFT+ENTER

    Note that M8 itself will be included
    Audere est facere

  5. #5
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    Re: Help to adjust date formula

    Thanks daddylonglegs that appears to resolve the issue.

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Help to adjust date formula

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED",as per our Forum Rule #9. I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

+ 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] Auto Adjust Stacked Column Need to Adjust Legend
    By tbren in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-29-2013, 01:56 PM
  2. Adjust the plan date if busy
    By bebo021999 in forum Excel General
    Replies: 8
    Last Post: 03-13-2012, 10:16 PM
  3. Adjust multiple date formats
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-09-2009, 10:01 PM
  4. Replies: 1
    Last Post: 01-16-2009, 05:31 PM
  5. Spinner box in Excel to adjust date
    By Mrebisz3154 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-28-2005, 07:05 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