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 20 May 2017

Article 45 - How to know if a Cell contains Time

By |Saturday, May 20th, 2017|Categories: Articles, VBA|Tags: , , , |0 Comments

We talked about Article 19 – How to Check if a cell contains a date. Now, in this article, I will talk about checking the cell for time.

The way Excel stores dates as numbers, in the same way it stores time as numbers only. Only change is in range. The numbers get stored between 0 to 1. Hence,  6 AM is stored as .25, 12 PM is stored as 0.5 and so on.

(more…)

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 >

 

 

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

Sat 31 Oct 2015

Tips & Tricks 115 - Insert Fixed Current Date and Current Time

By |Saturday, October 31st, 2015|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

We all are aware about today() and now() formulas which insert current date and current date/timestamp. But these change with every recalculation of your worksheet.

But if you want to enter the current date and time which doesn't change with recalculation i.e. it gets fixed, then following Excel shortcuts can be used.

Current Date - CTRL+:

Current Time - CTRL+SHIFT+:

Current Date & Time - To insert the current date and time, press CTRL+; (semi-colon), then press SPACE, and then press CTRL+SHIFT+; (semi-colon).

Sat 05 Sep 2015

Tips & Tricks 108 - Extract Date and Time from Date Timestamp

By |Saturday, September 05th, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Suppose you have a date timestamp value in cell A1

A1 = 06/14/15 10:15 PM

And you want to extract date and time out of this.

To extract date, use following formula and format the result cell as date

= INT(A1)

To extract time, use following formula and format the result cell as time

= MOD(A1,1)