Below is a possible solution to the Challenge 63 - Convert to Date Format
Put following formula and drag down
SUBSTITUTE(A1,",","")," ","*",2),"*",", "))
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
The solution to the above challenge will be published after a month i.e. on 10-Jul-17.
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.
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
To get Last Save Time, enter following in a cell
To get Last Save Date & Time Both, enter following in a cell
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 >
LastSaveDate = DateValue(FileDateTime(ThisWorkbook.FullName))
LastSaveTime = TimeValue(FileDateTime(ThisWorkbook.FullName))
LastSaveDateTime = FileDateTime(ThisWorkbook.FullName)
0 of 10 questions completed
A quiz on date functions of Excel
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
0 of 10 questions answered correctly
Time has elapsed
You have reached 0 of 0 points, (0)
The minimum date which can be inputted into Excel is
If A1=23-Aug-16, then the answer for =EDATE(A1,-2) would be
If A1=23-Aug-16, then what would be the answer for =MONTH(A1)
To calculate the number of completed months between two dates which function should be used?
If A1=23-Aug-16, what is the result of =EOMONTH(A1,3)
If A1=31-Jan-16, then what would be the result for =WEEKNUM(A1)
To get today's date, which function to use
If A1=23-Jan-16, what is the answer for following =EOMONTH(A1,0)+1
Which formula returns the number of whole workdays between two dates
Shortcut to apply date format is
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).