# Datediff functon calculate quarter to the hour

How can I get the DateDiff function to calculate to the quarter of the hour, as in 2.50 (i think that is how it should be displayed), in the example below:

DateDiff("h", "03/09/2010 7:00 AM", "03/09/2010 9:30 AM")

I tried "q" as a parameter and that didn't work.
LVL 2
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
try

DateDiff("s", "03/09/2010 7:00 AM", "03/09/2010 9:30 AM")/3600
0
Commented:
If what you really mean is, "I need the difference between two datetimes, measured in quarter hours", then stay away from DateDiff, which does not measure elapsed time at all, but rather it measures the number of time interval boundaries occurring between two datetimes.

Try this:

SELECT Datetime1, Datetime2, Int((Datetime2 - Datetime1) *24) + Switch(((Datetime2 - Datetime1) *24) - Int((Datetime2 - Datetime1) *24)) >= 0.75, 0.75, ((Datetime2 - Datetime1) *24) - Int((Datetime2 - Datetime1) *24)) >= 0.5, 0.5, ((Datetime2 - Datetime1) *24) - Int((Datetime2 - Datetime1) *24)) >= 0.25, 0.25, True, 0)
FROM SomeTable
0
Commented:
The formula I gave will assign 0-24 minutes to the "zero" quarter hour, 25-49 minutes to the "15" quarter hour, etc.

For rounding, we can adapt cap1's formula:

SELECT Datetime1, Datetime2, Round(4 * DateDiff("s", Datetime1, Datetime2) / 3600, 0) / 4 AS RoundedHrs
FROM SomeTable
0
TrainerCommented:
I can get to hours and minutes with Format(DateTime2, DateTime1, "hh.nn")
0
TrainerCommented:
in excel, this is neat:  =CONVERT(DateTime2 - DateTime1 ,"day","hr") does the trick providing decimal calculation as formatted
0
Commented:
DoDahD:  Type mismatch error.
0
TrainerCommented:
OOPS;  Format(DateTime2 - DateTime1, "hh.nn")
Since dates are already numbers, the difference is a dateserial
0
TrainerCommented:
You can convert the .nn minutes to decimal by multiplying it tiems 1.6667
0
Commented:
michael1174:  As I understand the question, what is the number of quarter hours between two datetimes where each datetime is first rounded to the nearest quarter hour?  If not so, please clarify.
0
Commented:
That should be:  As I understand the question, what is the number of hours between two datetimes where each datetime is first rounded to the nearest quarter hour?  If not so, please clarify.
0
Commented:
It is to say the decimal part of the answer will be to the quarter hour - 0.0, 0.25, 0.5. 0.75
0
CIOCommented:
If you wish to count the difference the same way as for hours and a quarter is either the first, second, third or fourth part of an hour of the day, which closely mimics the behaviour of DateDiff, you'll have to adjust the time as shown in the function below.

To obtain a "decimal" hour, just divide the output by 4.

/gustav
``````Public Function DateDiffQuarterHour( _
ByVal datFrom As Date, _
ByVal datTo As Date) _
As Long

' Returns difference between datFrom and datTo in quarters of the hour.
' 2007-01-07. Cactus Data ApS, CPH.

Const cintMinutesQuarter  As Integer = 15

Dim datDateFrom       As Date
Dim datDateTo         As Date
Dim datTimeFrom       As Date
Dim datTimeTo         As Date
Dim lngQuarterHour    As Long

datDateFrom = DateSerial(Year(datFrom), Month(datFrom), Day(datFrom))
datDateTo = DateSerial(Year(datTo), Month(datTo), Day(datTo))
datTimeFrom = TimeSerial(Hour(datFrom), Int(Minute(datFrom) / cintMinutesQuarter) * cintMinutesQuarter, 0)
datTimeTo = TimeSerial(Hour(datTo), Int(Minute(datTo) / cintMinutesQuarter) * cintMinutesQuarter, 0)
datFrom = datDateFrom + datTimeFrom
datTo = datDateTo + datTimeTo

lngQuarterHour = DateDiff("n", datFrom, datTo) \ cintMinutesQuarter

DateDiffQuarterHour = lngQuarterHour

End Function
``````
0
Author Commented:
cactus_data, I had to change your return type as a double because I wanted to return the decimal.. i had also divided by 4 like you suggested and your function worked.

GRayL, yes, i am trying to get the quarter of the hour...0.0, 0.25, 0.5. 0.75

capricorn1: thanks, it works.

matthewspatrick: thanks, for the rounding portion of capricorn1 forumula... since i am not using sql to get the result, i am not going to implement your first suggestion, but thanks.

Both capricorn1 and cactus_data forumla seems to work for me, so i guess it just comes down to which one i like...
0
CIOCommented:
No, you have to decide for what you wish to achieve.

It is either a time span counted in decimal hours rounded to 0.25 (or not?) - or it is a difference in quarters of the hour exactly as you, for example, have one hour difference between #08:50# and #09:10#. This is what my function does.

Also, the return data type of my function should not be changed as it returns integers only.
Your data type for a converted value to a decimal count of hours must, of course, be different to be able to hold fractions:

dblHours = DateDiffQuarterHour(datFrom, datTo) / 4

Note that this will - per definition by dividing an integer by 4 - be rounded to 0.25.

/gustav
0
CIOCommented:
Here is how to calculate the timespan in quarter hours as a decimal hour value.

To obtain the result rounded correctly up/down (at 5/5) to integer quarters, you can use this expression:

dblHours = Int(Format(QuarterHours(datFrom, datTo) * 4, "0")) / 4

The trick is that Format rounds correctly for any value - here to an integer.

/gustav
``````Public Function QuarterHours( _
ByVal datFrom As Date, _
ByVal datTo As Date) _
As Double

' Returns difference between datFrom and datTo in decimal quarters hours.
' 2007-04-13. Cactus Data ApS, CPH.

Const clngSecondsQuarter  As Long = 15 * 60

Dim lngSeconds      As Long
Dim dblQuarterHours As Double

lngSeconds = DateDiff("s", datFrom, datTo)
dblQuarterHours = lngSeconds / clngSecondsQuarter

QuarterHours = dblQuarterHours

End Function
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks!
0
CIOCommented:
You are welcome!

/gustav
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.