Sun 14 Jul 2019

Tips & Tricks 171 - Calculate Next Working day if date is of Weekend / Holiday

By |Sunday, July 14th, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you are given a date and you are asked to calculate next working day if date is of weekend. If date is a regular workday, then you should show the same date.

For example - 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or 10-Mar-19 which are Saturday and Sunday, then you must show 11-Mar-19 as the next workday. (more…)

Mon 10 Jul 2017

Solution - Challenge 63 - Convert to Date Format

By |Monday, July 10th, 2017|Categories: Solutions|Tags: , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 63 - Convert to Date Format

Put following formula and drag down

=IFERROR(--SUBSTITUTE(A1,",",""),--SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,",","")," ","*",2),"*",", "))

 

Sat 10 Jun 2017

Challenge 63 - Convert to Date Format

By |Saturday, June 10th, 2017|Categories: Challenges|Tags: , , , , , , , , , , , |1 Comment

This time, you have been given a file containing Text dates into various formats. The challenge before you is to write a single formula to convert them into an appropriate date format.

The challenge file can be downloaded from Challenge 63 - Convert to Date Format

1

The solution to the above challenge will be published after a month i.e. on 10-Jul-17.

Sat 06 May 2017

Downloads 15 - Excel Formulas Bible

By |Saturday, May 06th, 2017|Categories: Downloads|Tags: , , , , , , , , , , , , |0 Comments

This is one single document which contains close to 100 formulas dealing with various situations. Useful for Intermediate and Advanced users.

Download it from Excel - Formulas Bible

Sat 03 Sep 2016

Tips & Tricks 144 - Enter the Last Save Date and Time

By |Saturday, September 03rd, 2016|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

1. Open your workbook and ALT+F11
2. Locate your Workbook name in Project Explorer Window
3. Right click on your workbook name > Insert > Module
4. Copy paste the Macro code given
5. Save your file as .xlsm

To get Last Save Date, enter following in a cell
=LastSaveDate()

To get Last Save Time, enter following in a cell
=LastSaveTime()

To get Last Save Date & Time Both, enter following in a cell
=LastSaveDateTime()

Note - You will have to format your result cells appropriately in Date / Time / Timestamp format

< Download the workbook illustrating the same Last Saved Date Time >

 

 

Mon 18 Jul 2016

Solution - Challenge 45 – Number of Days Passed in a Quarter

By |Monday, July 18th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 45 - Number of Days Passed in a Quarter

The formula to calculate number of days passed in a quarter is

=A1-DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)-1)*3+1,1)

Fri 24 Jun 2016

Tips & Tricks 139 - Convert from Julian Dates to Excel (Gregorian) Dates

By |Friday, June 24th, 2016|Categories: Tips and Tricks|Tags: , , , |0 Comments

In Tips and Tricks 138, we covered conversion from Excel Dates to Julian Dates. Here, we want to look at reverse.

For 7 Digits Julian Dates, following formula should be used

=DATE(LEFT(A1,4),1,RIGHT(A1,3))

For 5 Digits Julian Dates, following formula should be used depending upon which century (Note - Julian dates are most likely to fall into 20th Century)

21st Century
=DATE(20&LEFT(A1,2),1,RIGHT(A1,3))

20th Century
=DATE(19&LEFT(A1,2),1,RIGHT(A1,3))

Note - 19 or 20 can be replaced with some IF condition to put in right 19 or 20 depending upon the year. For example, year 82 is more likely to be in 20th century where year 15 is more likely to be in 21st century.

Sat 18 Jun 2016

Challenge 45 - Number of Days Passed in a Quarter

By |Saturday, June 18th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

This time the challenge is - If a date is given, what would be the formula to find the number of days passed in a quarter.

If A1 has the value 12-Mar-16, then 31 days in Jan, 29 days in Feb (2016 is a leap year) and 11 days in Mar = 31+29+11 = 71 days have passed in Q1. Hence, answer is 71.

If A1 has 15-Apr-16, then 14 days have passed in Q2. Hence, answer is 14.

If A1 has 28-Aug-16, then, 31 days in Jul and 27 days in Aug = 31+27 = 58 days have passed in Q3. Hence, answer is 58.

You need to give the formula to find the above.

The solution to above challenge will be published after a month i.e. on 18-Jul-16.

Sat 11 Jun 2016

Excel Quiz 40 - Date Functions

By |Saturday, June 11th, 2016|Categories: Quizzes|Tags: , , , , , , , |0 Comments

Excel Quiz 40

A quiz on date functions of Excel

Fri 10 Jun 2016

Tips & Tricks 138 - Convert from Excel Date (Gregorian Date) to Julian Date

By |Friday, June 10th, 2016|Categories: Tips and Tricks|Tags: , , , |1 Comment

Q. First what is a Julian Date?
A. A Julian date has either 7 digits or 5 digits date and these are generally used in old IT legacy systems.
7 Digits - YYYYDDD - 2016092 (This is 1-Apr-2016. 92 means that this is 92nd day from 1-Jan in that year)
5 Digits - YYDDD - 16092

Q. What formulas to use to convert Excel Dates to Julian Dates?
A. For 7 Digits, use following formula
=TEXT(A1,"yyyy")&TEXT(A1-("1JAN"&YEAR(A1))+1,"000")

For 5 Digits, use following formula
=TEXT(A1,"yy")&TEXT(A1-("1JAN"&YEAR(A1))+1,"000")