This Excel tutorial explains how to change default Excel Date Format from Change Date and Time Format in Control Panel.
How to Change Default Excel Date Format
In the country I live, we normally use date format dd/mm/yyyy. However whenever I work for the US company, the system defaults to mm/dd/yyyy. This is annoying and it causes Excel error. For example, if I mistakenly type UK format 31/12/2019, the date will be recognized as a text because there is no such month as 31. It is even risky if ...
Read More
Date / Time
Excel VBA convert date period to date row
This Excel VBA tutorial explains how to convert date period to date row.
Excel VBA convert date period into date row
Different systems store date transactions differently. For example, if you apply for annual leave from Jan 1 to Jan 3, some system store the data in data base using date period
Begin Date
End Date
1/1/2018
1/3/2018
However some system store one date per row.
Begin Date
End Date
1/1/2018
1/1/2018
2/1/2018
2/1/2018
3/1/2018
1/3/2018
...
Read More
Excel compare time or apply condition on timestamps
This Excel tutorial explains how to compare time or apply condition on timestamps in Excel spreadsheet
Excel compare time or apply condition on timestamps
When we have two timestamps (a date with time) on the same date, we can use simple subtraction on the two timestamps to compare.
If the two timestamps have different dates, we can still minus the timestamps to compare.
But if we only want to compare the time only without considering the date, we need to extract the time portion of time...
Read More
Excel calculate year of service with different methods
This Excel tutorial explains how to calculate year of service in Excel using different calculation methods.
Excel calculate year of service
Having worked for human resources field for some years in different companies, I have seen different systems / companies use different calculation methods to calculate year of service for annual leave entitlement. In this post, I want to summarize how to use Excel to calculate year of service using different methods to help HR professionals.
Method 1 - ca...
Read More
Excel Days360 Function to calculate day difference
This Excel tutorial explains how to use Excel Days360 function to calculate day difference between two dates.
Excel Days360 Function
You can easily calculate the number of days between two dates by a simple deduction using Excel formula, however when you try to calculate the number of years between two dates, most people simply divide the day difference by 365. However, because every month has different days, and due to leap year, dividing the difference by 365 is totally incorrect.
Excel D...
Read More
Excel first date of month and last date of month
This Excel tutorial explains how to find first date of month, last date of month using Excel worksheet Function and Excel VBA Function.
You may also want to read:
Excel Use Date in IF Condition
Excel first date of month
To find first date of month in Excel, first we have to understand DATE Function, you can use it to combine the year, month and day to a date.
Syntax of Date Function
DATE( year, month, day )
Example
Formula
Result
Explanation
=Date(2012,1,1)
1/1/2012
Co...
Read More
Access Excel VBA day month year difference between two dates
This Access Excel tutorial explains how to find complete year difference, month difference and date difference between two dates.
You may also want to read:
Excel DATEDIF Function to calculate date difference
Access Excel VBA difference between two dates (year difference, month difference, day difference)
In Excel worksheet, there is a Function called DateDif that can calculate difference between two dates in complete year, month or day.
In VBA, there is no DateDif Function. There is ...
Read More
Excel convert column with Date and Text to Date
This Excel tutorial explains how to convert a column with Date and Text to Date.
Excel convert column with Date and Text to Date
Excel has a built-in Function DateValue, which converts a Date in Text format to date serial. What if the column contains both Text and Date? If you use DateValue Function on Date, #VALUE! will return. So how can we convert Text and Date at the same time?
Convert Text to Date in Notepad
The below method is even faster than DateValue Function or Text to Column, it i...
Read More
Excel Display Time from Date Time
This Excel tutorial explains how to use Custom Format to display Time from Date Time.
You may also want to read:
Excel Extract Time from Date Time
Excel Custom Format
Excel Display Time from Date Time
In my previous post, I have explained how to extract time from date time. What I mean "extract" is that the date is using formula to completely remove date from date time. In this tutorial, I will explain how to use custom format to display Date Time in Time format.
Custom Format to disp...
Read More
Excel VBA Application.OnTime Method to schedule event with timer
This tutorial explains how to use Excel Application.OnTime Method to trigger / schedule event at specified time.
You may also want to read:
Auto Open Excel with Windows Task Scheduler
Excel automatically refresh pivot table
Excel VBA Application.OnTime Method to trigger event at specified time
You can do the followings with Excel VBA Application.OnTime Method
- schedule a procedure to be run at a specified time in the future
- schedule a procedure to be run after a specific amou...
Read More