Date

[#1]

Hi Friends,

I have two different excels  IR dump and stratification Audit, with no relation

in IR dump there is date field, stratification Audit don't have.

I would like to create a relation b/w the dumps using date ..

so what i have done is  renamed the excel with prefix 12092015  and it became as 12092015 stratification Audit and extracted that.

I used both Filename() and filetime() to get the date field,

left(filename(),+9),

i got the output as 12092015.

now what happening is i am not able to format the date as 12/09/2015 and i want to add two days for that

Eg: date(12/09/2015)+2 =12/11/2015, so that i can make relation between two tables.

I used the below one, but it is not working..

Date(Date#(left(filename(),+9),'MM/DD/YYYY'),'DD/MM/YYYY'),

Date(Date#(left(filetime(),+9),'MM/DD/YYYY'),'DD/MM/YYYY')

please help me to do this.

[sunny_talwar]

May be this (Without slashes) and may be MM/DD/YYYY to convert it into date with slashes in the format MM/DD/YYYY assuming the date is December 9th, 2015.

Date(Date#(left(filename(),+9),'MMDDYYYY'),'MM/DD/YYYY'),

Date(Date#(left(filetime(),+9),'MMDDYYYY'),'MM/DD/YYYY')

UPDATE: Also I am not sure if you need the plus ( + ) sign before the 9, I would just use 9 without the plus sign

Date(Date#(Left(filename(), 9),'MMDDYYYY'),'MM/DD/YYYY'),

Date(Date#(Left(filetime(), 9),'MMDDYYYY'),'MM/DD/YYYY')

and to add 2 days:

Date(Date#(Left(filename(), 9),'MMDDYYYY') + 2,'MM/DD/YYYY'),

Date(Date#(Left(filetime(), 9),'MMDDYYYY') + 2,'MM/DD/YYYY')

[tamilarasu · Accepted solution]

Try,

Why are you using 9 in left function. You need to take 8 characters in the above case. Try

Date(Date#(Left(FileName(),8),'MMDDYYYY')+2,'DD/MM/YYYY')

[#4]

Thank you soo much Sunny,

i missed it

now my out put is 12/09/2015

i have added two days for that,

Date(Date(Date#(left(filetime(),+9),'MMDDYYYY'),'MM/DD/YYYY')+2)  it is coming as 11/12/2015 which is correct,

i want to change it to 12/11/2015 format, please suggest me how to do?

[jagan]

Hi,

Use 8 as second parameter in Left(), so that you will get only date part.

Date(Date#(left(filename(), 8),'MM/DD/YYYY'),'DD/MM/YYYY')

Hope this helps you.

Regards,

Jagan.

[#6]

hi Tamil i want it in 12/11/2015 format instead of 11/12/2015,

please suggest

[sunny_talwar]

I think Jagan brings a good point, MMDDYYYY are 8 characters, so you probably need just 8 within the left function. and to change to DD/MM/YYYY, the date function should use that format:

Date(Date#(left(filetime(), 8),'MMDDYYYY') + 2,'DD/MM/YYYY')

[tamilarasu]

Simply interchange the Date format.

Date(Date#(Left(FileName(),8),'MMDDYYYY')+2,'DD/MM/YYYY')

[#9]

Tamil,

it is coming as same there is no change if i mention MM/DD/YYYY  or DD/MM/YYYY

[tamilarasu]

Sowmya, I just tried the below and it's showing correct date format.

It would be helpful, If you could post a sample file.?