1
Cell A displays: general20130903.txt
Cell A formula: =CONCATENATE("general",YEAR(TODAY()),TEXT(MONTH(TODAY()),"00"),TEXT(DAY(TODAY()),"00"),".txt")
Cell B is pure text of value: \\asimple\uncpath
Cell C formula: =ChkFile(CONCATENATE(B43,"\",A43))
ChkFile is a very simple macro.
This set of affairs works fine for cases of Cell A being plain text but this calculated value results in Cell C showing #VALUE!
.
Can anyone help please and if possible without using VBA?
Edit: I didn't originally want to supply the macro code as I didn't feel it was relevant but also .. I'm no VBA programmer so not sure if I'm breaking any golden standards even in a couple of lines!
Public Function ChkFile(Name As String)
ChkFile = FileDateTime(Name)
End Function
It seems that the problem has to do with the very simple macro... As such, I don't think there's a solution not involving VBA. – Jerry – 2013-09-03T09:31:38.197
I did say if possible. Not sure why you think the macro is at fault when it works fine for scenarios of Cell A being plain text. If it is to do with the macro then surely it's just a matter of the input being sanitised somehow? – Paul – 2013-09-03T09:49:22.647
It seems so, because I can't see anything which could cause it :( I'm not VBA savvy either though, but it seems there's yet another function to look at... – Jerry – 2013-09-03T11:06:38.840
Well turns out the input was at fault! – Paul – 2013-09-03T11:24:45.523
just adding -1 to DAY part won't work on 1st of the month (because it won't set the month back 1 too) - try using
="general"&TEXT(TODAY()-1,"yyyymmdd")&".txt"
– barry houdini – 2013-09-03T15:45:35.487