Formula for Dates Calculation

rvaughan32

Hello,

I am trying to find out how I can create formulas for the below.

Kickoff Date - Number of rows with a kickoff date by month? IE Count of projects in a column with a kickoff date in Jan, Feb, March etc

Average days in IMPL - The average number of days for all projects. IE: kickoff day in my sheet vs current day for all rows in the sheet summed.

If I have 2 projects with different kickoff dates I want to be able to know the average time since kickoff of both summed in 1 number. Not sure if this is even possible or I would need to just calculate myself. I previously just created a "datedif" macro in excel to find the total days then divided it by the total projects

Paul Newcome

It looks like you have some syntax issues.

When referencing another sheet, you do not need to use {Range}:{Range} because {Range} already indicates to look at the column when you created it.

When referencing a date in a formula, you need to use the DATE function.

DATE(yyyy, mm, dd)

rvaughan32

My man, thanks Paul!

Paul Newcome

To get the counts you would use a COUNTIFS function. To get the averages, you would need to have a column that houses the total number of days on each row and then average this column with an AVG function.

rvaughan32

Thanks Paul, I am using the below but still getting unparseable errors. There are blank cells in the range, would that cause any issues? Screenshots below

=COUNTIFS({Kickoff Date}:{Kickoff Date}, >01 / 01 / 2022, {Kickoff Date}:{Kickoff Date}, <"01 / 31 / 2022")

Paul Newcome

It looks like you have some syntax issues.

When referencing another sheet, you do not need to use {Range}:{Range} because {Range} already indicates to look at the column when you created it.

When referencing a date in a formula, you need to use the DATE function.

DATE(yyyy, mm, dd)

rvaughan32

My man, thanks Paul!

Paul Newcome

Happy to help. 👍️