Help with SUMIF

Hugh McKinney

Hi I am calculating time spent on work by Job Number and by Date

I have set up a SUMIF formula to add time spent by job number:

=SUMIF([Job Nº]:[Job Nº], "64501", Time:Time) - Works fine.

the formula to add time spent by date is:

=SUMIF([Job Date]:[Job Date], "30/03/17", Time:Time) - and the calculation comes out as "0"

Not sure what is going wrong here, is it something to do with defining the date correctly?

Thanks in advance

 

Hugh McKinney

Just trying a couple of things to try and fix this.

I changed the "Job Date" column properties from "Date" to "Text/Number" and the formula worked!

 

I need to keep the column proerties as Date... Just need help in getting the formula to recognise the date in "Date" mode.

Adam Overton

Instead of "30/03/17" have you tried using the DATE(year, month, day) function?

 

Hugh McKinney

Tried that, with variations on date format... does not work unfortunetly!! Hmmm..... 

Hugh McKinney

Changed the format from

DATE(2017, 03, 28) to DATE(2017, 3, 28)

Removed the "0" from the month and this resolved the issue.

Thanks for your help... Much appriceated.