I have a column that has weekday (e.g. Monday) in one row and the time in the another row.
In another column I want those rows that have a time entered to be converted into the weekday. The weekday is in a text format. Can anyone help?:o
[Solved] VBA: Convert Date into Text
- kiwichico
- Closed
-
-
-
Can you post an example. I am not sure if by "time" you mean an Excel date or you mean a time of day in H:MM:SS format. If your "time" is a date, then you canuse the WEEKDAY() function in conjunction with VLOOKUP to get the day in text format. WEEKDAY(A1) returns a number from 1 to 7 indicating the day of the week. You can set up a table from 1 to 7 in column 1 and with the days in the adjacent 2nd column to pull in the day as text, if this is what you are after. An example of your data and what you want to end up with always helps give you the best answer to your question.
-
Thanks for your reply.
The guys who entered in the data made a meal of it. Here is an example of what I am trying to achieve.Old col New column
Day of the week Day
M M
M M
16:05 F
15:05 Th
W WUnfortunately the date should be recorded as 16:05 (dd:MM) but it is recorded as a time 16:05 (hh:mm), so the 16th is a Friday in this case.
-
are your 16:05 etc values held as text or numbers? (check with =isnumber() on one of the cells)
paddy
-
16:05 is held as time.
-
One, inelegant, method:
1) highlight the column, go to data | text to columns - hit next twice
2) select DMY as the column data format, hit finish.
3) 16:05 will now be held as 16/05/2000
4) convert this into values you want:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book5</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><U>F</U>ile <U>E</U>dit <U>V</U>iew <U>I</U>nsert <U>O</U>ptions <U>T</U>ools <U>D</U>ata <U>W</U>indow <U>H</U>elp <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb663764><INPUT onclick='window.clipboardData.setData("Text",document.formFb685530.sltNb227554.value);' type=button value="Copy Formula" name=btCb592514></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb685530><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value" name=sltNb227554><OPTION value='=IF(ISNUMBER(A2),TEXT(DATE(YEAR(A2)+3,MONTH(A2),DAY(A2)),"ddd"),A2)' selected>C2<OPTION value='=IF(ISNUMBER(A3),TEXT(DATE(YEAR(A3)+3,MONTH(A3),DAY(A3)),"ddd"),A3)'>C3<OPTION value='=IF(ISNUMBER(A4),TEXT(DATE(YEAR(A4)+3,MONTH(A4),DAY(A4)),"ddd"),A4)'>C4<OPTION value='=IF(ISNUMBER(A5),TEXT(DATE(YEAR(A5)+3,MONTH(A5),DAY(A5)),"ddd"),A5)'>C5<OPTION value='=IF(ISNUMBER(A6),TEXT(DATE(YEAR(A6)+3,MONTH(A6),DAY(A6)),"ddd"),A6)'>C6</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value='=IF(ISNUMBER(A2),TEXT(DATE(YEAR(A2)+3,MONTH(A2),DAY(A2)),"ddd"),A2)' name=txbFb761501></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">Day</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">Day</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">Derived</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">M</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">M</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[0].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">M</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">M</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">M</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[1].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">M</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">16/05/2000</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">F</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[2].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">Fri</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">15/05/2000</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">Th</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[3].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">Thu</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">W</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">W</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[4].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">W</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>
the formula is:
=IF(ISNUMBER(A2),TEXT(DATE(YEAR(A2)+3,MONTH(A2),DAY(A2)),"ddd"),A2)
-
Thanks that helps a lot.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!