4 Methods to Insert a Timestamp in Excel Cell

In the digital age, where data is king, effectively managing and recording time-related information is crucial. By effectively using timestamps in Excel, you can easily track when data entries are made or modified, serving as a cornerstone in tasks like project management, record keeping, and data analysis.

Read more

How To Calculate Hours Between Two Dates in Excel

Recently I was asked how to subtract time in Excel (time difference) or how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.

Read more

MOD Function Time Extract

Extract Time from datetime with the MOD Function in Excel

I had a reader comment on my last post about how to extract time from a date-time number using the MOD function. Simple really. The syntax is MOD(number,divisor). The MOD function returns the remainder after number is divided by divisor. A simple example is MOD(5,2), which equals one (1). It works like this: five (5) …

Read more


How to Extract Time from DateTime values in Excel

Understanding the Datetime Handling in Excel In Excel, date-time values are a blend of date and time, stored as serial numbers. Excel represents dates as sequential serial numbers where each number corresponds to a specific date, starting from January 1, 1900. Time is represented as fractional parts of a day. For instance, 0.5 represents noon, …

Read more

Extract Date from DateTime Number in Excel

I recently saw a spreadsheet with the following function =LEFT(A2,8) where cell A2 was equal to 08/06/12 12:23 PM. The user was trying to extract the Date from the Date-Time value. The problem for me was that the LEFT function returns a “text” value. Excel is good at recognizing text values that look like dates, …

Read more

Pay Periods and Funky PivotTable Controls

I like to use a PivotTable to figure out simple problems in Excel. So for this post I’m going to use Excel 2011 (Mac), where PivotTable controls look funky when compared to their Windows counterpart. Since I get paid every two weeks, certain months in a year will contain three pay periods. Planning future vacations …

Read more

International Short Date Formatting for the TEXT Function

I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range. Here’s what a US user sees: From: 6/6/2011 to 6/10/2011 Here …

Read more

Date Format Settings Excel Windows

Regional Date Formats in Excel

I recently returned from two weeks in Ireland where I installed a couple of Excel reporting systems and came across the dreaded regional date settings. My computer is set for the US region, whereas the client’s computers were set for the UK region. When I mention regional settings on my computer, I’m talking about Windows …

Read more