Calling all Excel Guru...Again!

[617660]

Alright, this time I made a file so you guys can see what I want to achieve:

https://onedrive.live.com/redir?resi...154C70!12160

So there are two tabs in this workbook. On the "Good" tab, the dates are converted to the first of the month. So for instance, 3/13/14 on the "Date" column will be 3/01/14 on the "MM/YY" column.

On the "Bad" tab however, the "MM/YY" looks the same as in the "Good" tab but still retains the same date value as in "Date" column. SO for instance, 3/13/14 on "date" is still 3/13/14 on "MM/YY".

The MM/YY on the "good" tab is the result I want. I did it once...remember it involved pasting to a clipboard but forgot how to do it again. Can anyone enlighten me? thank you.

 

[saberahul]

Alright, this time I made a file so you guys can see what I want to achieve:

https://onedrive.live.com/redir?resi...154C70!12160

So there are two tabs in this workbook. On the "Good" tab, the dates are converted to the first of the month. So for instance, 3/13/14 on the "Date" column will be 3/01/14 on the "MM/YY" column.

On the "Bad" tab however, the "MM/YY" looks the same as in the "Good" tab but still retains the same date value as in "Date" column. SO for instance, 3/13/14 on "date" is still 3/13/14 on "MM/YY".

The MM/YY on the "good" tab is the result I want. I did it once...remember it involved pasting to a clipboard but forgot how to do it again. Can anyone enlighten me? thank you.

Your file says it is deleted. Regardless, if you're trying to get the first of the month for a given date, you could try:

Say A1 is 3/13/2015. In B1, you would put:

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

If you would like to get the end of month, there's a built-in formula which would be = eomonth(A1, 0)

 

[617660]

wasn't aware of the eomonth formula. Awesome!

I just thought there is a way to copy and paste to lose the date value and only retain the date format.

 

[dyt1983]

edit: To remove personally identifying information not relevant to the thread.

 

[saberahul]

wasn't aware of the eomonth formula. Awesome!

I just thought there is a way to copy and paste to lose the date value and only retain the date format.

Not sure what you mean. If you want to maintain the "date" format, you can use Format Painter from the original to the new cell(s). You can also pick the Date format from the dropdown in your toolbar above.

 

[617660]

Nevermind. The EOMONTH function works wonder. My problem solved. Thanks!

 

[jaduff46]

FYI and future reference, you can also just Paste Special\Format