extracting & number of days

[REENA GUPTA]

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

.


My sincere thanks to all. Finally I am able to get the desired results. Please see the attached sheet

 

[vletm]

REENA GUPTA
There seems to be some challenges with dates.
Your files E-column dates are in format M/D/Y and
if You 'normally' use dates with D/M/Y-format.
... and if try to calculate 'normal way' those dates, it would be a challenge.
If E-column date would saved with dd-mmm-yyyy -format,
then it would be 'more clear'. (Everyone would know, which part presents month.)
I modified my solution.
Now, after You modify E-column 'Last Trans Time'-information
You'll get result.
No need to do nothing with Cell F3 date, it would be always current date.

 

[GraH - Guido]

Following formula works at my end:
=$F$3-INT(E7)
Curious if this works at your end @GraH - Guido @p45cal

It doesn't, as you already understood ;-) Indeed regional settings... It only "works" where the date M/D/YYY is a possible D/M/YYYY variant. Thus either giving a wrong result or throwing an error.
As p45cal suggested, with PQ this kind of conversion is very simple via a Change Type with Local. But not possible for the OP it seems.
Personally I try to avoid this situation by forcing exported dates in a standard format as YYYMMDD.