I ahve this forumla:-
=MONTH(DATEVALUE(F4&" 1"))&$S$2
I have given cell S2 the range name of “CurrentYear”.
How do I change my foruma to show th erange name?
thios does not work =MONTH(DATEVALUE(F4&" 1"))&$currentyear
any advice please?
Sorry.
Do you know anything about data types and formatting and formulas?
If so, you should at least tell
- What’s the content of F4?
- Is it text? Is it a formula result? (If so, what formula?)
- What do you intend / try to achieve?
- What shall the appending of a “1” preceeded by a space mean in the context?
- Why did you prefix a “$” sign to the range name? It can’t be part of the name itself.
- Why did you use a named range (or a named formula?) at all?
- Can you accept that the only date format
DATEVALUE()will reliably take independent of the locale (and some extras) is
YYYY-MM-DD?
If not so, it’s indispensable to learn about the basics.
2 Likes
Thank you for your reply.
I am given a .pdf file containing a table of the work programme for a group of volunteers* I have to convert this table into a .csv file for upload to a system. I have no control over the layout of the .pdf or the .csv files.
Having extracted the table from the .pdf I use Adobe to convert to an .xlsx file. This I bring into an .ods file to convert to a .csv format for upload.
The coverted .xlsx file cell A4 is as follows:-
day of month (1-31) then line feed
Day of week then line feed
time (24hour format)
e.g.
2
Wed
10:30
Note no month or year in this cell. Month comes from column F and year from cell S2; perhaps I don’t need to use a range name for the year?
The forumla I posted in my question was part of the actual formula I use to get the date format DD/MM/YYYY
the full formula is:-
=IF(ISNUMBER(VALUE(LEFT(A4,2))),LEFT(A4,2),“0”&LEFT(A4,1))&"/"&IF(MONTH(DATEVALUE(F4&" 1"))<10,“0”,“1”)&MONTH(DATEVALUE(F4&" 1"))&$S$2
If you could suggest a better formula to extract the date in DD/MM/YYYY format, from the supplied sources I would be grateful.
For time I use the formula:-
=LEFT(RIGHT(A4,5),2)&":"&RIGHT(A4,2) any suggestions to improve that HH:MM would agian be appreciated.
*These volunteers are very valuable and experienced in their work but, mostly, have little or no computer experience and do not wish to gain said experience. Often they pass their work schedule, by telephone, to someone who converts their conversation to the .pdf format. I was also “volunteeed” because I at least knew what a .csv file is; 20 years ago, before my retirement, I dealt with .csv files. I think you can see that none of us are youngsters; many are older than me.
Sorry but my reply seeems to have lost my line feeds when copy/pasted from Notepad. It looked fine in th e preview!
That’s the start of the description of a workflow looking extremely strange to me.
In fact I have little hope to get to the core of your problem based on the given information.
To be (possibly) able to suggest a solution or an improved workflow, I would need the pdf (an example) and the seemingly mandatory design of the csv.
Anyway:
- What you told looks as if your formula using the concatenation
... & $S$2worked for you. Why then change it? - The
$currentyearis an obvious syntactical error. The leading"$"when needing a reference to the range using the given name is definitely wrong.
BTW:
- I’m approaching 80.
- Somebody making
DD/MM/YYYYmandatory for dates in acsvmakes a mistake.csvfiles aren’t made for human reading but for reliable technical communication.
This is again an extremely error-prone workflow. Of course, the telephone submission must be accepted, but using a pdf editor (which one?) for entering data is sheer nonsense.
Why must it be csv now?
Such a jumble of representation formats is not easily manageable even for experts.
Anynody in charge of the final evaluation will most likely need to import the csv again to a different live representation where he can calculate with the data.
It’s a mess.
Thank you for your reply.
I will leave the formula as $S$2
I know I can’t change the work flow up to the .pdf; several have tried and met with, shall I say, “advice” that that would interfere with the work. So I am leaving that alone.
On a change of the system I have been provided with an Excel sheet into which I can enter the data then save that as .csv Problem is I can’t enter a formula into this spreadsheet; can only copy paste special TEXT from a cell containing the formula.
On the previous system I used my own setup to create .csv. I will just amend that to the new layout.
I note your age. Unlike me you kept up your coding skills. I retired and did other things.
I am not ignoring this but I am now on a short break till th emiddle of next week