Microsoft Query / Excel Date.Time

  • Thread starter Thread starter firesq38
  • Start date Start date
F

firesq38

Here is my problem. I have Microsoft Query running pulling data from a
datbase. The problem is when the times are pulled in I want it to drop
the date from the serial format (ddddd.ttttt) to be (0.tttt)

This way my formula calculates correctly. Where the data is being
entered the user checks a box and the time is entered and submitted.
If the user then corrects the time manually it erases the date in the
database. What I end of with is a mess of some entries as date/time
and some as just time and it messes with the formula.

Any ideas?
 
Modify your formula using mod(A1,1) where A1 is the cell with the time. A
date value uses whole number to count the number of days since Jan 1, 1900.
The time portion of the number is the fraction where an hour is 1/24 and a
minute is 1/(24*60). The mod function will leave only the fractional part of
the Time/Date value.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Microsoft Query is changing a negative number into a date 1
Macro or Look-up? 1
How to subtract a number from a Mergefield? 0
Elapsed Time returned from query 1
How do I convert date/time formats differently? 4
No date entered still pulls a reult '1' through?? 1
Formulas 3
Excel Dragging countif formula with changing criteria 3

Back
Top