| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 Column 'Date' to 3 Seperate Columns 'Month, Day, Year' How??
I used to have month, day and year entered in separate columns on my
€˜registrants sheet, when I did this I used this formula =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE))=TRUE,"----",VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE) ) to duplicate the respective values in separate columns on another sheet. However now the month, day and year on the €˜Registrants sheet are in one column as a common date (i.e. Dec. 12, 1996). How do I modify my formula to duplicate the year, month or day separately in cells on €˜Forms sheet? FROM: €˜Registrants sheet has month, day and year in one column as a common date (i.e. Dec. 12, 1996) TO: €˜Form sheet needs month, day and year entered in separate columns extracted from the column on the €˜Registrants sheet. Thanks Kerry |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
1 Column 'Date' to 3 Seperate Columns 'Month, Day, Year' How??
Something like the following:
=IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",YEAR(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE ))) =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",MONTH(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALS E))) =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",DAY(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE) )) ? Regards, Fred "Ksean" wrote in message ... I used to have month, day and year entered in separate columns on my €˜registrants sheet, when I did this I used this formula =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE))=TRUE,"----",VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE) ) to duplicate the respective values in separate columns on another sheet. However now the month, day and year on the €˜Registrants sheet are in one column as a common date (i.e. Dec. 12, 1996). How do I modify my formula to duplicate the year, month or day separately in cells on €˜Forms sheet? FROM: €˜Registrants sheet has month, day and year in one column as a common date (i.e. Dec. 12, 1996) TO: €˜Form sheet needs month, day and year entered in separate columns extracted from the column on the €˜Registrants sheet. Thanks Kerry |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
1 Column 'Date' to 3 Seperate Columns 'Month, Day, Year' How?
I knew it was something simple but I just couldn't put my finger on it.
Thanks Kerry "Fred Smith" wrote: Something like the following: =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",YEAR(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE ))) =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",MONTH(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALS E))) =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",DAY(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE) )) ? Regards, Fred "Ksean" wrote in message ... I used to have month, day and year entered in separate columns on my €˜registrants sheet, when I did this I used this formula =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE))=TRUE,"----",VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE) ) to duplicate the respective values in separate columns on another sheet. However now the month, day and year on the €˜Registrants sheet are in one column as a common date (i.e. Dec. 12, 1996). How do I modify my formula to duplicate the year, month or day separately in cells on €˜Forms sheet? FROM: €˜Registrants sheet has month, day and year in one column as a common date (i.e. Dec. 12, 1996) TO: €˜Form sheet needs month, day and year entered in separate columns extracted from the column on the €˜Registrants sheet. Thanks Kerry . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
1 Column 'Date' to 3 Seperate Columns 'Month, Day, Year' How?
Glad to help. Thanks for the feedback.
Fred. "Ksean" wrote in message ... I knew it was something simple but I just couldn't put my finger on it. Thanks Kerry "Fred Smith" wrote: Something like the following: =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",YEAR(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE ))) =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",MONTH(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALS E))) =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE)),"----",DAY(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE) )) ? Regards, Fred "Ksean" wrote in message ... I used to have month, day and year entered in separate columns on my €˜registrants sheet, when I did this I used this formula =IF(ISNA(VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FAL SE))=TRUE,"----",VLOOKUP(C9,Registrants!$A$3:$Y$1000,7,FALSE) ) to duplicate the respective values in separate columns on another sheet. However now the month, day and year on the €˜Registrants sheet are in one column as a common date (i.e. Dec. 12, 1996). How do I modify my formula to duplicate the year, month or day separately in cells on €˜Forms sheet? FROM: €˜Registrants sheet has month, day and year in one column as a common date (i.e. Dec. 12, 1996) TO: €˜Form sheet needs month, day and year entered in separate columns extracted from the column on the €˜Registrants sheet. Thanks Kerry . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count month when date is in day/month/year format | Excel Worksheet Functions | |||
| Separating dates from one column to Month, Date, Year columns | Excel Discussion (Misc queries) | |||
| Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
| seperate first 2 lines of column in seperate columns in same row | Excel Discussion (Misc queries) | |||
| Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
Linear Mode
