Excel: Days Function

Compute for the number of days between two dates

Description

=DAYS(end_date, start_date)

Parameters

  • end_date – end date for computation
  • start_date – start date for computation

Screen shot 2016 10 03 at 8.40.13 am

 Excel: Date Function

Create a valid date from year, month, and day

Description

=DATE(year, month, day)

Parameters

  • year – year to use when creating the date
  • month – month to use when creating the date
  • day – day to use when creating the date

Examples

Create date for August 8, 1982

=DATE(1982, 08, 21)

Create date for October 10, 2016

=DATE(2016, 10, 10)

Create date for April 21, 2000

=DATE(2000, 04, 21)

Create date for May 5, 1983

=DATE(1983, 05, 05)

Screen shot 2016 10 03 at 8.29.36 am

 Excel: SUM If Less Than

Formula to compute for values if cells have values less than a specified amount

Formula

=SUMIF(range, expression)

Add the values in cells if the value is greater than 3000

=SUMIF(A1:A8,"<3000")

Add the values in cells if the value is greater than 1000

=SUMIF(A1:A8,"<1000")

Add the values in cells if the value is greater than 500

=SUMIF(A1:A8,"<500")

Formula Parameters

  • Range – The first part of the formula lets you specify the range to add. This is specified like this, A1:A20
  • Expression – The second part of the formula lets you specify the condition. The condition is in string form. You can use multiple logical operators like <, =, <=, >=
=SUMIF(A1:A8,"<=2000")
=SUMIF(A1:A8,">=2000")
=SUMIF(A1:A8,"=2000")

Screen shot 2016 10 03 at 8.17.06 am

 Excel: SUM If Greater Than

Formula to compute for values if cells have values greater than a specified amount

Formula

=SUMIF(range, expression)

Add the values in cells if the value is greater than 4000

=SUMIF(A1:A8,">4000")

Add the values in cells if the value is greater than 2000

=SUMIF(A1:A8,">2000")

Add the values in cells if the value is greater than 1000

=SUMIF(A1:A8,">1000")

Formula Parameters

  • Range – The first part of the formula lets you specify the range to add.
  • Expression – The second part of the formula lets you specify the condition. The condition is in string form. You can use multiple logical operators.
=SUMIF(A1:A8,">=1000")
=SUMIF(A1:A8,"<=1000")

Screen shot 2016 10 03 at 8.11.00 am