Tag: Excel

Happy New Year 2019

 

happy new year message, keyboard enter key

By now, I am likely suffering the ill effects of an Egg Nog induced coma, so in my cognitive absence, I present the most read posts from the year. Enjoy and thanks for reading!

and because nothing cures a New Year’s hangover better than an AskQ post!

Most Read ASKQ Posts: 2018

Google Sheets Macros

macros
Google Sheets users can now record and execute macros. As every Excel user can attest to, this can be a invaluable when it comes to:

  • automate repetitive tasks
  • standardize data entry and data validation
  • execute a complex series of steps

Cheers!

quincy

Related Posts:

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!

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!

%d bloggers like this: