Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Convert Serial Number to a Date Function

  Asked By: Sebastion    Date: Mar 13    Category: MS Office    Views: 1880
  

I'm tryign to construct a SQL statement in Excel and my formula is as
follows:

="update M_DIMENSION1206294 set A_WeekNum = 117 where A_Week = '"&(B2)
&"'"


where B2 is a date cell that has the value of: 5/28/2005 00:00:00

However, my formula returns a serial number for the date instead of
the actual value. Which function can I use to return the actual date
and not the numerical value?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Virgil Foster     Answered On: Mar 13

I this will solve your problem, I've not found any single function  to convert
Number to Date, if any one knows pls. post on the group.

="update M_DIMENSION1206294 set  A_WeekNum = 117 where A_Week = '" & DAY(B1) &
"/" & MONTH(B1) & "/" & YEAR(B1) & "'"

 
Answer #2    Answered By: Penny Clark     Answered On: Mar 13

Can you try using TEXT(B2,"") ???????????

 
Answer #3    Answered By: Mohammed Evans     Answered On: Mar 13

="update M_DIMENSION1206294 set  A_WeekNum = 117 where A_Week =
'"&TEXT(B2,"yyyy-mm-dd")&"'"

Note that I always use the ISO yyyy-mm-dd format for SQL, as there is no
ambiguity between the various date  systems (dd/mm/yy, mm/dd/yy, yy/mm/dd) in use
in various countries.

 
Answer #4    Answered By: Abelard Fischer     Answered On: Mar 13

... & Text (Range ("B1"), "dd/mm/yy") & ...

"Text" in a formula  is similar to "Format" in VBA.

 
Didn't find what you were looking for? Find more on Convert Serial Number to a Date Function Or get search suggestion and latest updates.




Tagged:          

 
 
 
 

Related Post