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.
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)
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
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
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)
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.
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.
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
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
For 5 Digits, use following formula
I had already written about one formula where I dealt with coming day formula - Article 3 – Show Date for a Coming Day (e.g. Coming Saturday)
I wanted to revisit this with following things in mind -
1. I wanted to explore various formulas on the above topic. Here, I am giving 2 additional formulas. So, I will be talking about a total of 3 formulas.
2. In Article 3, I talked only about Coming Day not the Previous Day. In this article, I will be talking about formulas for Previous Day also.