datediff

KellyW

New Member
I have a datdiff function in a query to work out mins worked less 30 for lunch.
What i need to do is have the datediff fuction only if the result is a + figure ie they have been at work.
At the moment if i -30 mins and the result of the datdiff is 0 then i get a -
30 result which puts all other calculatoins out.
I tried the iif statement like this iif(datediff([suns],[sunf]=0,0,datediff([sun s],[sunf])
help

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Make sure you have your parentheses is the correct places. I would also try include "less than or equal to zero" to cover all bases of your requirement (greater than 0). Also make sure that you have suns and sunf in the correct order in the formula.

iif(datediff([suns],[sunf])<=0,0,datediff([suns],[sunf]))

Edit: Also notice that you have "suns" and "sun s" in the formula. I assume this is a typo. Make sure you have the correct name of the variables.

I have tried this and i get the error message " The expression you entered has a function containing the wrong number of arguments"
ray:

I didn't think to check to see if you had the datediff function set up right -- you don't. Datediff has 3 required arguments, the first being "interval" (see documentation in Help).

If you want the difference in minutes, try:

iif(datediff("n",[suns],[sunf])<=0,0,datediff("n",[suns],[sunf]))

THANK YOU IT NOW WORKS FINE
I CAN NOW GO HOME HAPPY

Replies
12
Views
379
Replies
5
Views
375
Replies
9
Views
212
Replies
5
Views
3K
Replies
18
Views
766

1,207,423
Messages
6,078,443
Members
446,339
Latest member
keogata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back