?
Solved

Format Excell Cell date

Posted on 2014-02-05
3
Medium Priority
?
399 Views
Last Modified: 2014-02-05
Hello,
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
Range("u1").Select
ActiveCell.FormulaR1C1 = "=TODAY()-1 + ""00:00:00"""
Range("u1").Select
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.

Range("u1").Select
Selection.NumberFormat = "[$-409]mm/dd/yyyy ""00:00:00"""
Range("u1").Select
Selection.NumberFormat = "[$-409]mm/dd/yyyy ""23:59:59"""

can you please help,
thanks
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 1200 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") & "')) "

Matt
0
 

Author Comment

by:W.E.B
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") & "'))
0
 

Author Closing Comment

by:W.E.B
ID: 39836804
Thank you.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

718 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