2 cells combined as date gives number instead of date

[Neil-B]

Hi all,

I have a problem using dates (month) and (Year)

I have a calc sheet that i am using as in invoice form and want to save the invoice with a saveasURL from a macro

all of this works ok, but i have a problem is with the URL based on cell A4

A1 I have made as the invoice date, say 24/1/17 and is formated as date.

A2 I have made =month(A1) and is formated as date and displays JAN.

A3 i have made =Year(A1) and is formated as date and displays 2017.

all is good upto this point, now the problem.

A4 is a cell that a saveasURL in a macro references, I am trying to incorporate cells A2 and A3 into the URL

it looks somthing like this ="//documents/invoice"&"/"&A3&"/"&A2&"/"&“myinvoice.ods”

what i would like is the URL to look like is //documents/invoice/2017/Jan/myinvoice.ods

but what i get is //documents/invoice/42759/42759/myinvoice.ods

I have tried formating A4 as date and anything else i could think with no luck.

is there any way of making cell A4 displaying properly

Thanks in advance Neil

[erAck]

MONTH(A1) is not a date, YEAR(A1) is not a date. If you format those numbers as date it’s some date resulting from the null date plus the number of days the formula expression returns.
However, your description doesn’t match those examples, with A1 date 2017-01-24, MONTH(A1) gives 1, that formatted as date has month December because serial date number 1 is date 1899-12-31; YEAR(A1) gives 2017, that formatted as date gives 1905-07-09 because serial date number 2017 is 1905-07-09. These again don’t match your example of “//documents/invoice”&"/"&A3&"/"&A2&"/"&“myinvoice.ods” because the 42759 is the serial date number of date 2017-01-24, so you have something different in cells A2 and A3 than in your example.

For your desired //documents/invoice/2017/Jan/myinvoice.ods and the date in A1 the formula expression would be "//documents/invoice/" & YEAR(A1) & "/" & TEXT(A1,"MMM") & "/myinvoice.ods".

[Neil-B]

Hi erAck,

thank you for your quick reply,

having re-read my question, you are correct my ref to A2 and A3 are not as described,

A2 and A3 both =A1 and both are formatted as date with A3 as “YYYY” and A2 as “MMM” but they both display the month and year as JAN and 2017 respectively

I have just tryed your answer and I get error 509 which i am just going to read up about now

I will have a read and try some other formulas

update

having read the error 509 it turns out when I copy and pasted your answer into my spread sheet i also picked up the full stop at the end, once i removed that everything worked perfectly.

Many thanks Neil

[mariosv]

If the answer solves your question please tick the .