Solved

Format Excell Cell date

Posted on 2014-02-05
3
380 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

813 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

15 Experts available now in Live!

Get 1:1 Help Now