1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

extracting & number of days

Discussion in 'Ask an Excel Question' started by REENA GUPTA, Sep 8, 2018.

  1. REENA GUPTA

    REENA GUPTA Member

    Messages:
    33
    Hi

    I have to calculate days from last transaction till date in the attached file. Not getting it. Tried to extract date but it is in text format. simple date subtraction formula not working.

    Please suggest how to do it

    Attached Files:

  2. deciog

    deciog Active Member

    Messages:
    113
    REENA

    Try this formula

    =$F$3-VALUE(LEFT(E7,SEARCH(" ",E7,1)-1))

    Decio
    Last edited: Sep 8, 2018
  3. REENA GUPTA

    REENA GUPTA Member

    Messages:
    33
    it is not working. file attached

    Attached Files:

  4. vletm

    vletm Excel Ninja

    Messages:
    4,299
  5. Syedali

    Syedali Active Member

    Messages:
    281
    PFA

    Attached Files:

  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    Or without helper columns in cell F7 type formula below and drag till end of column.
    [F7] = $F$3-DATE(MID(E7,FIND(" ",E7)-4,4),LEFT(E7,FIND("/",E7)-1),MID(E7,FIND("/",E7,1)+1,FIND(" ",E7,1)-5-(FIND("/",E7,1)+1)))

    Attached Files:

  7. p45cal

    p45cal Well-Known Member

    Messages:
    1,243
    This ridiculous formula in F7 copied down:
    Code (vb):
    =$F$3-(DATE(MID(E7,FIND("/",E7,FIND("/",E7)+1)+1,4),LEFT(E7,FIND("/",E7)-1),MID(E7,FIND("/",E7)+1,FIND("/",E7,FIND("/",E7)+1)-FIND("/",E7)-1))+TIMEVALUE(MID(E7,FIND(" ",E7)+1,20)))
    (At least it only refers to one cell in the part that converts the text string to a date!)
    It returns a value in days, including fractions of a day; you can round up/down.

    Ideally, you'd convert your strings in column E (and H?) into real excel dates which is usually no problem with Text To Columns but in this instance it doesn't convert all the dates (it seems when there is a time element in the string it doesn't like 2 digits in the day part).
    You could also use Power Query to do this date conversion but it's convoluted, it does however convert all date/times properly.
  8. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    Hi p45cal,
    Ideally the date comes out of the source system as a date or at least in a format directly workable in Excel.
    I noticed TTC was not working, nor a couple of other formula functions, so since date is stored as text, I reverted to text functions. How ridiculous indeed.
  9. REENA GUPTA

    REENA GUPTA Member

    Messages:
    33
    Thank you all for your suggestions but it is not working. Problem is not with the formula but with the way the date in e column is stored.

    Though Date is entered as mm/dd/yyyy, but it is taking it as a dd/mm/yyyy while calculating formula. that is why the result is not coming correct. So before applying formula please suggest how to correct the date format.

    Thanks in advance
  10. pecoflyer

    pecoflyer Active Member

    Messages:
    258
    Select the range starting in E7 ( alternatively H7)
    Data - Text to columns - Check " Delimited" - Next - Check space - Next - Select the first column and check Date DMY - Select the other columns one at a time and check " do not import" - Finish
    Now you have clean dates
  11. REENA GUPTA

    REENA GUPTA Member

    Messages:
    33
    Hi pecofler

    Thanks . I tried the the solution suggested but the problem is as mentioned above.
    Though Date is entered as mm/dd/yyyy, but it is taking it as a dd/mm/yyyy while calculating formula. that is why the result is not coming correct. So before applying formula we have to correct the date format.
  12. Haz

    Haz Active Member

    Messages:
    114
    =DATEDIF(DATE(MID(SUBSTITUTE(0&E7,"/"," 0"),8,5)+0,MID(SUBSTITUTE(0&E7,"/"," 0"),1,3)+0,MID(SUBSTITUTE(0&E7,"/"," 0"),4,3)+0),$F$3,"d")
  13. vletm

    vletm Excel Ninja

    Messages:
    4,299
    REENA GUPTA
    This would be one possible ...
    Change F3 -date or any 'Last Trans Times'-value ...

    Attached Files:

  14. p45cal

    p45cal Well-Known Member

    Messages:
    1,243
    I think you would need to choose MDY rather than DMY - this is telling Excel how the source data is arranged.

    Here in the UK I tried DMY and only got a removal of the time element. The cells remained formatted as Text and the dates as text strings.
    Using MDY I got true dates formatted in my locale date format.
    Last edited: Sep 8, 2018
  15. pecoflyer

    pecoflyer Active Member

    Messages:
    258
    I use the regional settings for Belgium. BTA I usually try the MDY version and if it doesn't work the DMY ( which did it for me in this case)
  16. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,918
    Following formula works at my end:
    =$F$3-INT(E7)

    Curious if this works at your end @GraH - Guido @p45cal
  17. p45cal

    p45cal Well-Known Member

    Messages:
    1,243
    DMY yielded questionable results for me in the UK, but MDY returned 100% correct interpretation (Column A):
    upload_2018-9-9_11-49-0.png
    Left justified results are strings, right justified results are Excel dates (just numbers after all). Columns C, D & E all returned incorrrect dates.
    I think Belgian dates are DMY as in the UK.

    Just for fun (as I'm exploring Power Query) a little macro that will convert American style date/time strings to Excel dates, including the time element, in situ. Just select those dates (1 column only) and run it.
    Code (vb):
    Sub blah()
    'This works on the selection (if you have Power Query), it doesn't need to make a table of the source data and it doesn't matter if the source data is in a table to start with.

    Application.ScreenUpdating = False
    Selection.Name = "myRng"
    Set Qry1 = ActiveWorkbook.Queries.Add(Name:="QueerryZ", Formula:="let Source = Excel.CurrentWorkbook(){[Name=""myRng""]}[Content], #""Changed Type with Locale"" = Table.TransformColumnTypes(Source, {{""Column1"", type datetime}}, ""en-US"") in  #""Changed Type with Locale""")
    Set NewSht = ActiveWorkbook.Worksheets.Add
    With NewSht.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & Qry1.Name & ";Extended Properties=""""", Destination:=NewSht.Range("A1"))
      With .QueryTable
        .CommandType = xlCmdSql
        .CommandText = "SELECT * FROM [" & Qry1.Name & "]"
        .Refresh BackgroundQuery:=False
      End With
      Range("myRng").NumberFormat = "d/m/yyyy h:m:ss"    ' AM/PM" 'This can be any format.
     Range("myRng").Value = .DataBodyRange.Value
    End With
    Qry1.Delete
    Application.DisplayAlerts = False: NewSht.Delete: Application.DisplayAlerts = True
    ActiveWorkbook.Names("myRng").Delete
    Application.ScreenUpdating = True
    End Sub
    Of course it'll crash on you; there's bound to be some problem!
    Hopefully some Power Query person can show me a better way.

    REENA's file is an Excel 2007 file so Power Query probably won't be available.
    Last edited: Sep 9, 2018
  18. p45cal

    p45cal Well-Known Member

    Messages:
    1,243
    @shrivallabha, in the UK it gave:
    upload_2018-9-9_12-22-29.png
    It seems to be defaulting to DMY (3rd column is just =INT(E7) formatted as a date) when the source strings are MDY.

    I suspect this will work in US date locales, and you could probably include the time element too with =$F$3-E7
    Last edited: Sep 9, 2018
  19. vletm

    vletm Excel Ninja

    Messages:
    4,299
    shrivallabha
    Curious 1: Do =$F$3-INT(E7) works with Cell E8 better?
    Curious 2: What would be answer to F7 (61 or 1)?
    Screen Shot 2018-09-09 at 14.25.50.png
  20. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,918
    @p45cal Location settings seem to handle things differently.

    @vletm I understood nothing. Here's a screenshot as I am having with my formula and p45cal's formula in post#7.

    upload_2018-9-9_15-39-28.png
  21. vletm

    vletm Excel Ninja

    Messages:
    4,299
    shrivallabha
    Try this ...
    You offered =int(E7) to solve E-columns value -- okay?
    What value it gives?
    Here, it gives 43290 (O-column) = 09-Jul-2018 (P-column) -- okay?
    >> E7 month is Aug and day is 7 = 07-Sep-2018 << -- okay
    Note: F3 date is 08-Sep-2018 (= yesterday)
    Cell E8 has value 8/30/2018 = 30-Aug-2018
    if takes INT(E8) then #VALUE! ... won't understand date correct.
    Screen Shot 2018-09-09 at 15.35.40.png
    I've added 'my results' to F-column
    F7: 'yesterday' - 07-Sep-2018 = 1 (Your formulas result is -29)
    F8: 'yesterday' - 30-Aug-2018 = 9 (Your formulas result is -9)
    ... and so on (as in #13Reply)
    > Questions? <
    sathishsusa likes this.
  22. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,918
    @vletm

    First of all, as I have noted in my post regional settings seem to affect the results.

    Please make a note of simple fact that OP's date in Post #1 (which you are referring to) and in Post #3 (the one I'm referring to) are different.
    Post #1 : Contains formula TODAY() in cell F3
    Post #3 : Contains no formula but hard coded dates.

    If I use same reference date as yours i.e. 08-Sep-18 -->
    and use calculate -->
    then I get below results -->
    upload_2018-9-9_17-51-37.png

    Do you think they match your results? --> Yes, No
    >Am I being clear?<
  23. vletm

    vletm Excel Ninja

    Messages:
    4,299
    shrivallabha
    > Your #20 Reply's Screenshot
    Here's a screenshot as I am having with my formula and p45cal's formula in post#7.
    You let understand that those are Okay ...
    eg from 9/7/2018 to 'today' was over 20 days.
    > Your #22 Reply's Screenshot
    then I get below results
    For my eyes, those #22 Reply's results looks better and
    with Your term - match - Yes.
    >> It only needs to match, how those work with OP's sheet! <<
    Clear ... seems to be clear,
    I tried to answer all You questions.

    > It's interesting that Int-functions works .. as it works ..
    I got 'INT'-results as in #21 screenshot as I tried to answer for Your #16 Reply.
    = all INTs gave incorrect result ... or not wanted!
    sathishsusa likes this.
  24. REENA GUPTA

    REENA GUPTA Member

    Messages:
    33
    Did not understand . but the results you showed r correct
  25. REENA GUPTA

    REENA GUPTA Member

    Messages:
    33
    Thanks . I am able to convert my last transaction date to dd/mm/yyyy. but not able to get the desired results.

Share This Page