[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 947

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.
0
michael1174
• 4
• 4
• 4
• +3
3 Solutions

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

Author Commented:
Thanks!
0

CIOCommented:
You are welcome!

/gustav
0

Featured Post

• 4
• 4
• 4
• +3
Tackle projects and never again get stuck behind a technical roadblock.