+ Reply to Thread
Results 1 to 9 of 9

Deduct Dates - Drop Down List

  1. #1
    Registered User
    Join Date
    11-27-2022
    Location
    Sydney
    MS-Off Ver
    2209
    Posts
    4

    Deduct Dates - Drop Down List

    Hey,

    I need help and not sure if it entirely possible.

    I have a huge tracker, which needs to be filtered by Low, Medium and High. I have created a drop down list for these items, however these drop down lists need to contain a formula, which will deduct a certain amount of days from a start date.

    For example

    Low - deduct 14 days
    Medium - deduct 30 days
    High - deduct 60 days

    For example: Column N - I have a start date of the 24/11/2022, I have selected a drop down list of a Low priority, I need excel to automatically deduct 14 days and input that date into another column.

    Is that possible?

    I hope this makes sense
    Attached Images Attached Images

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: Deduct Dates - Drop Down List

    sure, something like =IF(cell where your priority is = "Low",N2-14,IF(cell where your priority is ="Medium",N2-30,IF(cell where your priority is ="High",N2-60,"")))
    untested of course and where it says cell where your priority is would be the cell where your priority is and I'm using cell N2 as the example for your date cell.
    substitute those as they are appropriate to your data.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    11-27-2022
    Location
    Sydney
    MS-Off Ver
    2209
    Posts
    4

    Re: Deduct Dates - Drop Down List

    Could you please show me a snapshot of what you mean. I have tried, but its saying the formula is invalid (I am probably doing it wrong)

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: Deduct Dates - Drop Down List

    well I don't know what formula you are using nor where your data is located nor what exactly your dates and text looks like.
    the best thing is if you can upload a sample workbook of your data WITH expected results (nothing confidential or propiratory) by following the instructions to upload a workbook that are in the yellow banner at the top of this post. That is the best thing because otherwise I'm guessing (and I'm lazy and don't want to try to create a workbook based on your snapshot so I can take a snapshot to post).

  5. #5
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Deduct Dates - Drop Down List

    imo, instead of using dropdown I'm prefered to use formula for this case.
    by create range of words and deduct day for sample range G3:H5

    G3=Low
    H3=14
    G4=Medium
    H4=30
    G5=High
    H5=60

    and I will use this formula to calculate for sample at row 9

    =IF(F9>0,IFError(F9-Vlookup(D9,$G$3:$H$5,2,false),""),"")

    or just
    =IFError(F9-Vlookup(D9,$G$3:$H$5,2,false),"")


    Regards.

  6. #6
    Registered User
    Join Date
    11-27-2022
    Location
    Sydney
    MS-Off Ver
    2209
    Posts
    4

    Re: Deduct Dates - Drop Down List

    Morning!

    I won't lie you lost me, but I have attached the sample of my workbook. Maybe I am trying to over complicate it, but I need it for work purposes and it will make my life alot easier!

    Thanks
    Attached Files Attached Files

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: Deduct Dates - Drop Down List

    I'm still not sure as you didn't put in the workbook expected results because in your note in cell Q9 notes you want the durations noted in 4 rows to be subtracted by start date in col O.
    Are you looking for something in E9, E10, E11 and E12? If so, what results do you expect in each of those cells?

    but at least for now from what I can see this might be what you need...
    =if(d9="low",O9-14,if(d9="medium",o9-30,if(d9="high",o9-60,"")))
    then format as date. I put that formula in cell E9

  8. #8
    Registered User
    Join Date
    11-27-2022
    Location
    Sydney
    MS-Off Ver
    2209
    Posts
    4

    Re: Deduct Dates - Drop Down List

    You are a genius, that is exactly what I was after!

    Thank you so much I really appreciate it! That has made my life so much easier haha

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: Deduct Dates - Drop Down List

    Great! AND thank you for the rep!
    Last edited by Sam Capricci; 11-28-2022 at 05:54 PM.

+ 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. Deduct 6 working days from array of dates
    By danny25 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2020, 02:49 AM
  2. Replies: 3
    Last Post: 11-15-2019, 07:54 PM
  3. [SOLVED] Additional charge or deduct price, drop downs, vlookups
    By Excell_Ensie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2017, 03:34 PM
  4. Drop down list to deduct a value
    By vanillafreak12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2013, 11:57 AM
  5. Excel 2007 : Excel 2007 - Auto Deduct On Certain Dates?
    By scaramonga in forum Excel General
    Replies: 1
    Last Post: 06-04-2008, 03:42 AM
  6. Drop-down list of dates from the last 2 weeks
    By Capslock in forum Excel General
    Replies: 4
    Last Post: 09-26-2007, 01:38 PM
  7. Drop Down List:choices of various dates
    By dtmd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-18-2005, 01:10 PM

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