Hi all,
I am using Excel 2010. I have a cell with a formula in it, that involves showing a date multiple times, with carriage returns. The formula is below...sorry I know it is ugly, but it is very specific because I am exporting this from Excel, and importing this into another system, so this is how the format must be.
My problem is that the date will not show up (or export) as a DATE. The serial number shows up instead. Is there a way to change the formula (or change formatting) to have the date show up in the cell?
Here is the formula below. Spreadsheet is also attached.
C2 is where I manually enter the date, and this column is formatted as a date.
B2 is a picklist, which matches another worksheet cell. If there is a match, I am pulling in the date is C2 multiple times with a carriage return.
E2 is where I want the date showing, not the serial number. This column is formatted as a date as well.
=IF(B2='816 826-Admit Attend Location'!$B$1,C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2,IF(B2='816 826-Admit Attend Location'!$B$2,C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&C2&CHAR(10)&C2&CHAR(10)&C2,IF(B2='816 826-Admit Attend Location'!$B$3,C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2,IF(B2='816 826-Admit Attend Location'!$B$4,C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2,IF(B2='816 826-Admit Attend Location'!$B$5,C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2,IF(B2='816 826-Admit Attend Location'!$B$6,C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2,IF(B2='816 826-Admit Attend Location'!$B$7,C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2&CHAR(10)&C2,"")))))))
Bookmarks