Return a value from a cell based on today's date

David Bouchard

Hello everyone,

I am creating a metric sheet to track the total hours, attendees, and person hours of meetings and would like to track it based on today's date.

In the example above, I would like to use TODAY() in the top row under the "Date" column. Then if the date in the rows below are in the past, return the corresponding values in columns "Total Running Hours", "Total Running Attendees", and "Total Running Person Hours" for the largest values. So if today is 02/06/24, then the top row should return the values listed for "Meeting 3" since it contains the largest values and there are multiple meetings that occurred on 01/30/24.

Thank you in advance.

-David

Linda Manduchova

Hi @David Bouchard

I would recommend to use the following cell formulas for the respective columns:

• Total Running Hours: =SUMIF(Date:Date, <TODAY(), [Duration (Hrs)]:[Duration (Hrs)])
• Total Running Attendees: =SUMIF(Date:Date, <TODAY(), [# of Attendees]:[# of Attendees])
• Total Running Person Hours: =SUMIF(Date:Date, <TODAY(), [Person Hours]:[Person Hours])

See attached for an example where the formula is in the top row of the Total Runing Hours column.

Linda

Linda Manduchova

Hi @David Bouchard

I would recommend to use the following cell formulas for the respective columns:

• Total Running Hours: =SUMIF(Date:Date, <TODAY(), [Duration (Hrs)]:[Duration (Hrs)])
• Total Running Attendees: =SUMIF(Date:Date, <TODAY(), [# of Attendees]:[# of Attendees])
• Total Running Person Hours: =SUMIF(Date:Date, <TODAY(), [Person Hours]:[Person Hours])

See attached for an example where the formula is in the top row of the Total Runing Hours column.

Linda

David Bouchard

@Linda Manduchova, That is perfect! Very much appreciated.

Now that I see the formulas, I'm not quite sure why I was struggling with it so much.