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!

Speak Thai, Latin and Arabic with Excel.

ROMAN-Starwars.gif

Ever wonder what your birthday looks like expressed in roman numerals? Want to display your travel expenses using Thai?

BAHTTEXT.gif

Use these functions to convert numbers to their respective lingo.

Formula Description Example Result
BAHTTEXT Converts number to Thai text =BAHTTEXT(451) สี่ร้อยห้าสิบเอ็ดบาทถ้วน
(Four Hundred and Fifty-one)
ARABIC Convert Roman Numeral to Arabic =ARABIC(MCDXCII) 1492
ROMAN Convert Number to Roman Numeral =ROMAN(1984 , [0] )* MCMLXXXIV

*ROMAN, has an optional argument, format . When format is included, the number is expressed differently, ranging from classic (0) to simplest (4). When this argument is omitted, the classical format is displayed.

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

Use Advanced Search Wildcards to Format Names: Last, First Middle

lastnames-first-gif

Reformatting a list of names is challenging when some of the names have middle names and others do not. Using Word’s Advanced Replace dialog and wildcards makes it easy to rewrite a list in Lastname, First M. format.

3 minute e-Learning demo:

To Reformat a List of Names to Last, First M.

  1. Select your list of names.
  2. Press CTRL+H. The REPLACE dialog appears.
  3. If necessary click More button to display full dialog.
  4. Check Use Wildcards.
  5. In the Find what field type: (*) ([! ]@)^13
    Important: observe spaces.
  6. In the Replace with field type: \2, \1^p
    Important:
    note the space after the comma.
  7. Click Replace All. When prompted to “..continue searching the remainder of the document” click No.
  8. Close the dialog.

Why it works

The find pattern looks for two groups:  first name with optional middle name or initial and last name.

  • (*)_ finds expression1, first and middles names
  • ([!_]@)^13 finds expression2, the remainder of name up to and including hard return (ascii char 13)

These names can now be referred to as expressions \1 and \2. In our replace statement we  transpose them,  separate them with a comma and space, and end with a hard return (^p).

  • \2,_\1^p 

Note:  _ is used above to illustrate use of space.

Codes that work with Find and Replace

To find Type
Paragraph mark ( Paragraph mark ) ^p (doesn’t work in the Find what box when the Use wildcards option is turned on), or ^13
One or more occurrences of the previous character or expression @
ex: lo@t finds lot and loot.
Any single character except the characters in the range inside the brackets [!_]
ex: t[!a-g]ck finds tock and tuck, but not tack or tick.

Comments and questions are always welcome!

Cheers!

hɔuᴉnb

Related:

Happy Birthday, VisiCalc!

cvjvo3axyaab1s1

Excel, wish your granddad a happy birthday  🎂

 

 

%d bloggers like this: