Forum Discussion
Cell format
Thank you so much for your response, it still shows the date as mentioned in column D. I want the column C date format to be appear in column D as mentioned in next column "My desired value...". (I can get my desire value by converting the column D's cell as Date Format or as a text, but I want the value in plain Number or General format of the cell as mentioned in the next column "My desired value..." Like capturing the data in column C and pasting it to the column D without "=" and coma signs and without Date format of the cell). Thank you.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Yes, here's the link. Thank you
https://docs.google.com/spreadsheets/d/1eNnhy3oOqCgBOL8lL3TI_5ZFFHN20U-7/edit?usp=sharing&ouid=111964740657991279311&rtpof=true&sd=true
The problem is that:
A1 and A2 contain numbers (of days) formatted as =0\,
A4, A5 and A7 contain dates formatted as=mmddyy,
A8, A10 and A11 contain dates with custom format =yymmdd,
So you have two completely different types of values, with three different types of formatting.
You cannot expect a single formula to handle that. (It could probably be done with VBA)
In C1, simply use =A1
In C4, use =--TEXT(A4,"mmddyy")
In C8, use =--TEXT(A8,"yymmdd")
Format column C as General.
- Thank you so much for your response. With the formulas above it appears fine on my end as I mentioned I can fix the data appearance by Date or Text function but not the cell formatting. Therefore when I paste the values into company's original file which is in general format, the values don't show correctly. I think that's because the formula contains "text" so it handled as a text even though the cell formatted as General. Any solution as a Macro will be greatly appreciated. Thank you so much