Time and Date

Extracting Integers and Fractions in Microsoft Excel

February 14, 2013 Formulas

Sometimes you need to extract the integer portion of a number. Sometimes the fractional part. Sometimes both. Excel makes it easy to get the integer and somewhat harder to get the fraction. If you just want the answer, skip to the technical details. The Integer PartWhat could be easier than the INT function? I mean [...]

Get the full story …

Extract Time with the MOD Function in Excel

November 1, 2012 Formulas

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) [...]

Get the full story …

Extract Time from a Date-Time Number in Excel

October 9, 2012 Intermediate

I have a worksheet that tracks start and stop times for different events throughout the day, all during the week. Sometimes I have to pull out the Time of Day, irrespective of the Date, with the TIME function. The TIME function has three arguments: Hour, Minute, Second. I could use =TIME(11,30,0) in a cell to [...]

Get the full story …

Extract a Date from a Date-Time Number in Excel

September 15, 2012 Intermediate

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, [...]

Get the full story …

Pay Periods and Funky PivotTable Controls

October 31, 2011 Intermediate

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 [...]

Get the full story …

International Short Date Formatting for the TEXT Function

July 7, 2011 Advanced

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 [...]

Get the full story …

Regional Date Formats in Excel

June 21, 2011 Beginner

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 [...]

Get the full story …

Quickly Adjust 1904 Dates for the 1900 Date System in Excel

March 22, 2011 Intermediate

An associate of mine “helped” me out once by putting a worksheet together for data entry. After the worksheet had several days of user input, I found out the workbook was created on a Mac with an older version of Excel for Mac (2004) that defaulted to the 1904 date system. Since the worksheet in [...]

Get the full story …

A Woopra Chat About Excel

February 23, 2011 Formulas

I use the Woopra desktop app to view live visitors to this WordPress blog. Visitors will see a Woopra window lurking about when I’m logged into my (free) Woopra account. One option is to initiate a chat session with me. My first chat happened the other day. The chat notice startled me, but I quickly [...]

Get the full story …

Let Excel Convert Hours Between Two Dates and Times

February 14, 2011 Formatting

In a previous post I went on and on about how you can calculate hours between two different dates and times, but Excel does this for you automatically. I’ll use reader comment as my example. A-02.02.2011 10:00 B-05.02.2011 17:00 Please help me to find B-A in hh:mm For clarity I will assume that the date [...]

Related Posts Plugin for WordPress, Blogger...
Get the full story …

← Previous Entries