Dates & Months

[moni_tm]

Good Morning,

This is probably quite easy but i'm not having much luck.

Is there a way that if in column A1 to A12 I have the months of the year in their full name (January) I can get columns B1 - B12 to show the corresponding 1st day of the month in a DD/MM/YYYY format?

Any help is greatly appreciated.

Tom

[ashishmehra2010]

Hi,

Please check the attached file if this works.

Regards,
AM

[azumi]

Its very kind if you show the sample workbook for clear explanation, but here the tips, if you have "January" on cell A1, this return the first day of the month of the current year:

=DATE(YEAR(TODAY()),MONTH(A1&1),1)

[moni_tm]

Superstar, thank you.

Do you know if the dates in column B will update the year automatically? for example in January next year column b will show 01/01/2016?

Thanks again.

Tom

[ashishmehra2010]

Hi Tom,

Use

Please Login or Register  to view this content.

Regards,
AM

[moni_tm]

Thank you both. The below formula is exactly what i need.

=DATE(YEAR(TODAY())+1,MONTH(A1&1),1)

Tom

[daddylonglegs]

It should be sufficient to use this

=(A1&1)+0

format in required date format

[ashishmehra2010]

Thanks for the rep Tom

[kmahesh]

daddylonglegs your formula is not working , I think it should be

=(A1&15)+0 for 2015
=(A1&16)+0 for 2016 and so on