Midyear Roundup: My Best Dates

Some of my fave Excel Date Functions. Some old, some new; all designed to help track the passage of time.

Date Functions:

EOMONTH
returns the last day of the month
EOMONTH ( start_date , month )
EDATE
returns the date x number of months before/after a given date
 EDATE ( start_date , months )
NETWORKDAYS
calculates the number of working days
(see also NETWORKDAYS.INTL)
 NETWORKDAYS ( start_date , end_date , [holidays] )
TODAY
returns the current date
 TODAY( )
WEEKNUM
returns serial number for a given week
 WEEKNUM ( date , [week_start] )
WORKDAY
calculates the next valid work day
(see also WORKDAY.INTL)
WORKDAY( start_date, days, [holidays] )

 

YEARFRAC
returns the date as a fraction of the year
 YEARFRAC ( start_date , end_date , [basis])

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

Advertisements

Networkdays.INTL

As my international followers have reminded me, not every place sets aside two whole days for the weekend, nor does everyone use the same days of the week.
  • For Afghanistan, Egypt, and Pakistan their weekend starts Thursday at sunset, running from Friday through Saturday.
  • In Malaysia, based upon which part of the country your in, it’s either Saturday thru Sunday, or Friday thru Saturday.
  • Brunei Darussalam‘s non-contiguous weekend is on Friday and Sunday.
  • Costa Rica, North Korea and Uganda get only one day, Sunday, as a weekend.

As a follow-up to last week’s post here’s the international solution to calculating workdays.

NETWORKDAYS.INTL

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.

Syntax

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

  • Start_date and End_date (required). The dates for which the difference is to be computed.
  • Weekend (optional). A number value indicates the days of the week that are weekend days and are not included in the number of working. Below are the values and their corresponding weekdays.
Number Value Weekend days
1 or omitted Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday
4 Tuesday, Wednesday
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17

Saturday only

Holidays (optional). Indicates the dates that are considered non working days and not counted in the number of working days.

Cheers!

hɔuᴉnb

Additional reading:

 

 

Calculating Total Working Days (NETWORKDAYS)

Calculate the difference between two dates, exempting the weekends and holidays, using =NETWORKDAYS

Using NETWORKDAYS function:

  • Format: =NETWORKDAYS ( date1, date2, holidays)
  • date1 must be the earlier of the two dates.
  • holidays is an optional argument

Cheers!

hɔuᴉnb

Additional reading:

Comments questions (and magik tricks) are always welcome!

Hidden Excel: Find Date Differences with DATEDIF

In honor of the 40th anniversary of J.K. Rowling’s seminal classic, Harry Potter, this post magically exposes a hidden excel function!

Date Difference between J.K. Rowling’s Birthday and Harry Potter Movie Debut

Ever wonder if your spreadsheet is keeping something from you?

DATEDIF is an under-documented function. It allows  you to calculate the difference between two dates. Although it doesn’t appear in the Function Wizard (it exists to insure backward compatibility with Lotus and earlier Excel versions) it is a powerhouse when it comes to calculating date differences.

Using DATEDIF function:

  • Format: =DATEDIF ( date1, date2, “interval”)
  • date1 must be the earlier of the two dates or an #NUM error is returned.

Interval Arguments:

y Years
m Months
d Days
ym Months, years excluded
yd Days, years excluded
md Days, years and months excluded

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

%d bloggers like this: