Format Excell Cell date

Posted on 2014-02-05
Last Modified: 2014-02-05
can you please help,
I use a code to get information form SQL to Excel.
one of the lines in the code is
"WHERE((OrderDate >= '" & sh.[u1] & "')  And (OrderDate <='" & sh.[v1] & "')) "

if I format the cells like this, my Code works
ActiveCell.FormulaR1C1 = "=TODAY()-1 + ""00:00:00"""
ActiveCell.FormulaR1C1 = "=TODAY()-1 +""23:59:59"""

the issue I have is I need to be able to change the date in the cell.
so if I format the cells to below, my code will not work.

Selection.NumberFormat = "[$-409]mm/dd/yyyy ""00:00:00"""
Selection.NumberFormat = "[$-409]mm/dd/yyyy ""23:59:59"""

can you please help,
Question by:W.E.B
  • 2
LVL 35

Accepted Solution

mvidas earned 300 total points
ID: 39836033
How about using the format function when pulling the date from excel?

"WHERE((OrderDate >= '" & Format(sh.[u1], "mm/dd/yyyy hh:mm:ss") & "')  And (OrderDate <='" & Format(sh.[v1], "mm/dd/yyyy hh:mm:ss") & "')) "


Author Comment

ID: 39836801
thanks Matt,
this worked.

((OrderDate >= '" & format(sh.[u1], "mm/dd/yyyy 00:00:00") & "')  And (OrderDate <='" & format(sh.[v1], "mm/dd/yyyy 23:59:59") & "'))

Author Closing Comment

ID: 39836804
Thank you.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now