#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Within a SUMIFS statement, can you extract the hour from a timestamp and use it as criteria?

Options
edited 12/09/19

Hello all,

I have a problem that has been driving me mad - I would be grateful of some help!

Column A contains names (text string)

Coumn B contains costs (numbers)

Colum C contains a timestamp in the format dd/mm/yy hh:mm:ss

I want to be able to create a formula that says "add up all the costs from Column B, where Column A contains 'DAR', and the hour is less than 12pm regardless of what the day/date is"

I used the following fomula, but I keep getting a 0 returned:

=SUMIFS(B:B,A:A,"*DAR*",C:C,HOUR(C:C)<12)

The dates in the C column are across multiple days, but essentially I am trying to 'ignore' the days and add up all costs that happen before 12pm - irresepctive of what day they happen on.

I also tried using a SUMPRODUCT statement, but that equally drove me to the brink of insanity.

I would be most grateful if someone could help.  I have a tigh deadline coming up, so any help as soon as possible would be much appreciated!

Thanks and kind regards

Darren

Tags:

• Employee
edited 02/13/17
Options

Hello Darren,

We currently don't have a SUMPRODUCT formula in Smartsheet, and there isn't a way to work with time values (we don't have an HOUR formula) but I've got your vote down for this on our enhancement request list for further consideration.

More information on our available functions in Smartsheet can be found in our Help Center: https://help.smartsheet.com/articles/775363-using-formulas

• ✭✭✭✭✭✭
Options

Darren,

This formula should solve your problem:

=SUMIFS(B:B, A:A, FIND("DAR", @cell) > 0, C:C, LEFT(RIGHT(@cell, 8), 2) < 12)

assuming column C is strictly formatted.

Craig

This discussion has been closed.