Filename extract string as date

[#1]

Hi Guys

I load in a few Excel Files to my QV doc see example of pathway,

S:\Support\Supports\Weekly to Monthly\19-06-2013-Workflow.xls

For each of these files I have a field 'Name' based on the file name see :

left(filename(),10) as Name

But I need this as DATE format rather than just the text value (as I wish to later on join in with a Time field for another table)

Can anyone advise me how I may do this ?

Thanks

A

[swuehl]

If you successfully managed to cut out the string '19-06-2013', then

Date#('19-06-2013','DD-MM-YYYY') as Date

should interprete your string as date.

[#3]

Thanks a mill for that -

I am looking to join that Date with an already existing time format - so see sample data below :

The problem is I have a field Curr_Txn_Date and this SHOULD hold the date and the time in the format below ,(:mm:ss in excel)

But in some instances there is ONLY the Time in this field -see data for Orange - so in this instance I wanted to extract the Date of the file as the default value for the Date portion - by using the above method and then in the code was hoping to write some sort of identifier like

if(len(Curr_Txn_Date)<8, left(filename(),10)&Curr_Txn_Date,Curr_Txn_Date)

or something like that

What do you think - as you can see the code as Date#() is not giving the format I require

Any advice greatly appreciated

Thanks

A

Product_Id	Curr_Activity_Description	Curr_Txn_Date	Date	code
55001	Apple	994668:49:40	19-06-2013	19-06-201341444.534490243
55001	Orange	0:37:49	18-06-2013	18-06-20130.023122685185185
55001	banana	994668:49:24	19-06-2013	19-06-201341444.534309722
55001	pineapple	0:33:18	18-06-2013	18-06-20130.023122685185185
55001	tomatoe	994668:49:26	19-06-2013	19-06-201341444.534309722

[swuehl]

Maybe like

Timestamp(

if(Curr_Txn_Date < 1, date#( left(filename(),10),'DD-MM-YYYY') + Curr_Txn_Date, Curr_Txn_Date)

,'DD-MM-YYYY hh:mm:ss')

as code

[#5]

Hey Swuel,

Its still not quite doing what I had hoped in excel : ( Say cell B1 had the filename)

Date formula =DATE(MID(B1,7,4),MID(B1,4,2),LEFT(B1,2)) ( Can you advise how I may do THIS is QV load)

Then I would use your formula as below and format as :mm:ss

=IF(LEN(C2)=17,D2+C2,C2)

Worksheet Name	Original data	Date Formula	Conditional Sum
14-06-2013 work	11:55:41	14/06/2013	994547:55:41
14-06-2013 work	994547:55:33	14/06/2013	994547:55:33
17-06-2013 work	8:27:07	17/06/2013	994616:27:07

Any help would really be appreciated

Thanks

A

[#6]

DATE#

(Day(left(filename(),2))&month(mid(filename(),4,2))&year(mid(filename(),7,4)),'DD/MM/YYYY')

I've tried variations of this and I just cant quite get it right for the Date formula

[swuehl · Accepted solution]

Day(), Month() and Year() need a valid date as argument to return something meaningful.

And your format code must match the string you pass (note the '/' delimiter in your format code).

Try

Date#( left(filename(),2)&mid(filename(),4,2)&mid(filename(),7,4), 'DDMMYYYY')

while I would still prefer something like posted above.

If you have trouble getting this to work, proceed step by step, i.e. first try to cut out the date string from the filename, stop and have a look at the result, then create an appropriate format code and use date#() to interpret this date string.

Finally, use the interpreted date to create something else, like adding a time part and format as interval in hours:

=interval ( Date#() + TimeField, 'hh:mm:ss')

[#8]

OMG Swuel

It was the "/" in my format DD/MM/YYYY

I cant believe I didnt realise that - thanks again for all you help and time

A

[#9]

if(Curr_Txn_Date < 19, Timestamp(Date#( left(filename(),2)&mid(filename(),4,2)&mid(filename(),7,4), 'DDMMYYYY') + Curr_Txn_Date,':mm:ss'), Curr_Txn_Date)as Number

This was the solution

[#10]

if(Curr_Txn_Date < 19, Timestamp(Date#( left(filename(),2)&mid(filename(),4,2)&mid(filename(),7,4), 'DDMMYYYY') + Curr_Txn_Date,':mm:ss'), Curr_Txn_Date)as Number

This was the solution