• 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...

  • 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

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
 

Attachments

deciog

Active Member
REENA

Try this formula

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

Decio
 
Last edited:

GraH - Guido

Well-Known Member
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)))
 

Attachments

p45cal

Well-Known Member
This ridiculous formula in F7 copied down:
Code:
=$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.
 

GraH - Guido

Well-Known Member
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.
 
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
 

pecoflyer

Active Member
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
 
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.
 

Haz

Active Member
=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")
 

p45cal

Well-Known Member
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
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:

pecoflyer

Active Member
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)
 

p45cal

Well-Known Member
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)
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:
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:

p45cal

Well-Known Member
Following formula works at my end:
=$F$3-INT(E7)

Curious if this works at your end @GraH - Guido @p45cal
@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:

shrivallabha

Excel Ninja
@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
 

vletm

Excel Ninja
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? <
 

shrivallabha

Excel Ninja
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.
View attachment 55088
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? <
@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?<
 

vletm

Excel Ninja
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!
 
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.
Thanks . I am able to convert my last transaction date to dd/mm/yyyy. but not able to get the desired results.
 
Top