# extracting & number of days

#### REENA GUPTA

##### Member
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

• 41.5 KB Views: 16

#### deciog

##### Active Member
REENA

Try this formula

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

Decio

Last edited:

#### REENA GUPTA

##### Member
it is not working. file attached

#### Attachments

• 41.7 KB Views: 11

PFA

#### Attachments

• 56 KB Views: 5

#### 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

• 41 KB Views: 2

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

#### REENA GUPTA

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

#### 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

#### REENA GUPTA

##### Member
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")

#### vletm

##### Excel Ninja
REENA GUPTA
This would be one possible ...
Change F3 -date or any 'Last Trans Times'-value ...

#### Attachments

• 38 KB Views: 4

#### 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)

#### shrivallabha

##### Excel Ninja
Following formula works at my end:
=\$F\$3-INT(E7)

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

#### 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):

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""")
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
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:

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:

#### vletm

##### Excel Ninja
shrivallabha
Curious 1: Do =\$F\$3-INT(E7) works with Cell E8 better?
Curious 2: What would be answer to F7 (61 or 1)?

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

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

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

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

#### vletm

##### Excel Ninja
shrivallabha
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.
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 ..
= all INTs gave incorrect result ... or not wanted!

#### REENA GUPTA

##### Member
REENA GUPTA
This would be one possible ...
Change F3 -date or any 'Last Trans Times'-value ...
Did not understand . but the results you showed r correct

#### REENA GUPTA

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