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...
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...
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...
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...
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 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 a similar Function, DateDiff, which is used to calculate date difference but it does n...
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...
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...
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...
More

Excel Custom Format in Format Cells

This Excel tutorial explains how to use Excel Custom Format in Format Cells to format date, time, number, text, conditional formatting, leading zero, etc. You may also want to read: Excel Extract Time from Date Time Excel Display Time from Date Time What is Excel Custom Format? Excel guesses the most appropriate format for the cells when you type something in a Cell. For example, if you type 31/1/2014, Excel guesses it would be a Date, Format is dd/m/yyyy, but it would not guess it is...
More

Excel EDATE Function to add month

What is Excel EDATE Function? Excel EDATE Function is to add (or subtract) specified months to a date. For example, adding 2 months to Jan 14, 2015 would be Mar 14, 2015. Syntax of EDATE EDATE(start_date, months) start_date A date to add months to months Positive integer to indicate to add month to start_date, negative to indicate subtraction By default, a Cell Format is "General" which does not display a date as date format (such as mm/dd/yyyy), instead date is displayed...
More