Format date cells

[wmbb]

Is it possible to show the date in the cells in the format (weeknr) jjjj-mm-dd bij formatting the cells?
With other words can I show the dates in the cells like (01) 2016-01-01 and (05) 2016-02-01 instead of just 2016-01-01 and 2016-02-01 ?

 

[zelgar]

The resultant will be Text, but you can do it with the following formula:
="("&WEEKDAY(A1)&") "&TEXT(A1,"yyyy-mm-dd")

 

[Andrzejek]

zelgar,
Your formula will return:

[pre]
A B
1/1/2016 (6) 2016-01-01
2/1/2016 (2) 2016-02-01
[/pre]
I assume by "(weeknr)" the OP means "week of the year", but I could be wrong.

But if I am not, try this formula:
[tt]="("&WEEKNUM(A1,2)&") "&TEXT(A1,"yyyy-mm-dd") [/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Hi,

Excel has no WEEK format. Ther is, however a WEEKNUM() function that you can use in a separate column.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

If you accept conversion to string, you can use vba Format function, optional argument can be used to adjust first day of week:

ActiveCell.Value = Format(ActiveCell, "(ww) yyyy-mm-dd")

combo

 

[wmbb]

Thanks for your options.
At this moment I use the option

[COLOR=#CC0000]=WEEKNUM(A1;21)[/color]

in a separate cell but I want to combine the date and weeknumber in one cell.
It doesn't matter if it is converted to a string.

1) The option ="("&WEEKNUM(A1,2)&") "&TEXT(A1,"yyyy-mm-dd") works for me
2) I don't know how to use the option of combo, can you explain a little bit more how to use the function ?

 

[combo]

Sorry, this code could be used if you perform conversion with vba.
Do you get #VALUE error as a result or the posted formula? If so, check the contents of the cell wirh general format, you should get a number.
After using any of the above methods to join week number and date, one gets string that is not recognised as date, so it can't be used as date input in formulas.

combo

 

[wmbb]

Hi Combo,
The error in option 1 was caused by a stupidity of my side but it works for me now.
I have worked with vba functions but do I have to loop through all cells using the function to format the cells in the format I like ?

Oh Oh, a little problem...
It seems to be be a problem to convert the dates to text because the conditional formatting of the sheet uses the date cells and there is also a navigation in one of the vba formula's to start the sheet at today's date cell.

So I changed the solution and show the week number of the selected column using a button and a vba procedure.
Thanks for your support.