# Formula for Dates Calculation

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

Tags:

• ✭✭✭✭✭✭

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)

My man, thanks Paul!

• ✭✭✭✭✭✭

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.

• 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")

• ✭✭✭✭✭✭

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)