Datediff("h",Begn,End) ?

Datediff("h",Begn,End) ?

This is what I get now

The problem (or one of the problems) you're running into is that the day ends at 11:59:59 PM [23:59:59], not 12:00:00 AM [00:00:00]. The day begins at 12:00:00 AM [00:00:00]. 

So you're not working from

07/04/2025 10:00:00 PM [22:00:00] to 07/04/2025 12:00:00 AM [00:00:00]; 

You're working from 

07/04/2025 10:00:00 PM [22:00:00] to 07/05/2025 12:00:00 AM [00:00:00].

Paul is correct and also pay attention to the way dates are entered or you might not be getting the dates you expect.

Your Begin Time has a properly formatted 4 digit year, yet your End Time does not and I suspect it is not the date you expect it to be in calculation.  Just look at the last recordset to get -1100247 that puts the date back to 1899, not 2025.

Also, n is for minutes, if you want hours you need to use h, refer to: https://support.microsoft.com/en-us/office/datediff-function-e6dd7ee6-3d01-4531-905c-e24fc238f85f 

Lastly, in your expression #12:00 PM#, that doesn't represent today at 12:00 PM, but rather 12/30/1899 12:00:00 hence why you are getting the results you are.

@Paul, @Daniel,

Lets start from the beginning…

I was tasked with splitting a record that starts at one day and finishes at the next day.

So I started designing a union query which looks like this.

SELECT [Holiday Split].[Begin Time], Format([End time],"mm/dd/yy"" 00"":""00""") AS Expr1, Abs(DateDiff("n",CDate(DateValue([begin Time])+1),[end time])/60) AS Expr2
FROM [Holiday Split]


WHERE (((Day([begin time]))<>Day([end time])))


union all


 SELECT Format([End time],"mm/dd/yy"" 00"":""00""") AS Expr1, [Holiday Split].[End Time], Abs(DateDiff("n",CDate(DateValue([begin Time])+1),[end time])/60) AS Expr2
FROM [Holiday Split]
WHERE (((Day([begin time]))<>Day([end time])));

Select allOpen in new window

And below are the results

At the moment, it seems to make sense, except for the 474.5.

Any better suggestions?

From your screenshots, I'd say you are heavily depending on Access to correctly interpret what you have for dates and times as text values.  Judging from your results, I'd say you are expecting too much and incorrectly.

To properly have Access correctly interpret a string date/time, it needs to be in a format both the date and time can be correctly interpreted. 

Convert the text values to date values (CDate()) and I think you'll see why you are getting the results you are getting. 

Try wrapping your string values in the CDate() function and see what you are getting for date/time values.

Correct your input data as necessary.  DateDiff() function requires values that have both date and time (00:00 AM is understood if there is no time element).

If the datetime values are strings, they need to have month, day, 4-digit year, and AM or PM on the times to be safe.

@Mark, @Dale,

See results I get from Dale's query.

@Dale,

But not sure what are you trying to accomplish, how will that solve the issue I am having with calculating the duration as posted above?

@Mark,

Getting same results using cdate(), see below.

SELECT cdate([begin time]), Format(cdate([end time]),"mm/dd/yy"" 00"":""00""") AS Expr1, Abs(DateDiff("n",CDate(DateValue(cdate([begin time]))+1),cdate([end time]))/60) AS Expr2
FROM [Holiday Split]


WHERE (((Day(cdate([begin time])))<>Day(cdate([end time]))))


union all


 SELECT Format(cdate([begin time]),"mm/dd/yy"" 00"":""00""") AS Expr1, cdate([end time]), Abs(DateDiff("n",CDate(DateValue(cdate([begin time]))+1),cdate([end time]))/60) AS Expr2
FROM [Holiday Split]
WHERE (((Day(cdate([begin time])))<>Day(cdate([end time]))));

Select allOpen in new window

BeginTime = #07/05/2025 13:00#
EndTime = #07/06/2025 08:25#

Duration = CDate(EndTime - BeginTime)
? Duration
19:25:00 

DurationText = Format(EndTime - BeginTime, "h:nn")
? DurationText
19:25

TotalHours = (EndTime - BeginTime) * 24
? TotalHours
 19.4166666667443 

Select allOpen in new window

You are getting the same result “using CDate()” because you are using it inside the Format function resulting in your input parameters still being text data type.  Your values in your columns should be date/time values like 7/2/2025 00:00:00 AM, not 7/2/25 00:00 which is still a text value.  Make your values in the columns Expr1000 and Expr1 as datetime values, not text.  The DateDiff() function is designed to use date/time data type as input parameters, not text.

It seems like Dale suggestion works.

SELECT  [Begin Time], [End Time],DateDiff("n", [Begin Time], [End Time]) / 60 as duration FROM  [Holiday Split]
WHERE DateValue([Begin Time]) = DateValue([End Time])
UNION
SELECT  [Begin Time], DateValue([End Time]) as [EndTime],DateDiff("n", [Begin Time], [EndTime]) / 60 as duration
FROM  [Holiday Split]
WHERE DateAdd("d", 1, dateValue([Begin Time])) = DateValue([End Time])
UNION
SELECT  DateValue([End Time]) as [BeginTime], [End Time] , DateDiff("n", [BeginTime], [End Time]) / 60 as duration
FROM  [Holiday Split]
WHERE DateAdd("d", 1, dateValue([Begin Time])) = DateValue([End Time]);

Select allOpen in new window

Hi Experts,

Maybe you can help with the related question

Modify schedule duration according to limit of billable hours | Experts Exchange

I need to get the only the billable part of the duration of that split query.

Thanks