Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm having a file name where I should be extracting timestamp from the file.
However I'm able to convert to date but not time stamp
My file format is XYZ_20_11_2016_00_12
by using string functions I'm able to extract date, 11/20/2016 but its throwing error for time stamp.
I'm using date(date#)) but it is not working for timestamp(timestamp#))
any suggestions
surprise part is, if I hardcode and do its working
=timestamp(timestamp#(right('XYZ_20_11_2016_00_12',16),'DD_MM_YYYY_HH_MM'),'M/D/YYYY HH:MM:SS tt')
but not if I replace file name (
Hi Swe,
Please try using this,
=timestamp(timestamp#(replace(mid('XYZ_20_11_2016_00_12',5,10),'_','/')&' ' & replace(mid('XYZ_20_11_2016_00_12',16,5),'_',':'),'DD/MM/YYYY hh:mm'))
How are you using timestamp(Timestamp#(... ; what is it giving you as output and what do you expect?
it's not giving anything.In textbox it's just showing -
Which expression are you using and what format code are you providing?
Try simple
=Timestamp(Timestamp#(mid('XYZ_20_11_2016_00_12',index('XYZ_20_11_2016_00_12','_')+1),'DD_MM_YYYY_hh_mm'))
Replace XYZ_20_11_2016_00_12 with your fieldName.
Regards
Try this, May be?
=Date(Date#(Purgechar(Left(Replace(KeepChar('XYZ_20_11_2016_00_12', '0123456789_'),'_', '/'),11),'/'),'DDMMYYYY'),'MM/DD/YYYY')
If the string is the same lenght all the time:
timestamp(timestamp#(right(FIELD,16), 'DD_MM_YYYY_HH_MM'),'YYYY-MM-DD HH:MM:SS') as ts
surprise part is, if I hardcode and do its working
=timestamp(timestamp#(right('XYZ_20_11_2016_00_12',16),'DD_MM_YYYY_HH_MM'),'M/D/YYYY HH:MM:SS tt')
but not if I replace file name (
I did exactly same, it works but if replace FIELD with actual name its not working