How to add 2 fields which contains hours

[VKKT]

HI all,
Greetings!

In a query I want to add two fields with hours which is calculated as below:

NormalHours: Format(DateDiff("n",[N-Start],[N-Finish])/60/24,"h:nn") = 1.40
OTHours: Format(DateDiff("n",[OT-Start],[ot-Finish])/60/24,"h:nn") = 2.45

if I add the above 2 fields as below:

TotalHours:([normalhours])+([othours]), (is this the correct way?)

I am getting the result as 1:402:45

Please give a resolution for the above.

regards,
VKKT

[HansV]

Format returns a text string. The + operator for text strings acts like &. It concatenates the strings. So 1:40 and 2:45 are simply placed next to each other. You want to work with the actual times instead of with text strings.

Change the definitions:

NormalHours: [N-Finish]-[N-Start]
OTHours: [OT-Finish]-[OT-Start]
TotalHours: [NormalHours]+[OTHours]

Set the Format property of all three fields to h:nn

[VKKT]

Thanks Hans,

But when we need to format we have to add ()?

like OTHours: Format([ot-Finish]-[ot-start],"h:nn")

With above formula, i am getting the same result

Thanks

[HansV]

No, I really meant

OTHours: [OT-Finish]-[OT-Start]

Click in this column of the query. then activate the Property Sheet (F4).

Set the Format property to h:nn

S2683.png

(Alternatively, click the drop-down arrow in the Format property and select Short Time)

[VKKT]

Greetings!

Thanks Hans, it is working well

But while calculating Total Hours: [NormalHours]+[OTHours], when use Nz function if one field is nil, it is giving a different result.

like =Nz([NormalHours]+[OTHours],0)

How this can be solved? Please advise.

Thanks, VKKT

[EileenStifone]

=Nz([NormalHours],0)+Nz([OTHours],0)

[VKKT]

Thanks EileenStifone & Hans,

Yes, It is working

regards, VKKT