Solved

Format Excell Cell date

Posted on 2014-02-05
3
382 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
  • 2
3 Comments
 
LVL 35

Accepted Solution

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

839 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