2010 August | Excel Semi-Pro

# August 2010

## Date and Time Calculation in Excel

August 31, 2010 Beginner

The unit of time in Excel is the Day. By using the NOW Function to show both date and time, you can see the underlying serial number by changing the cell format to General. This serial number is composed of two parts: the date part is the integer and the time part is the decimal. […]

Get the full story …

## The NOW Function in Excel

August 28, 2010 Beginner

The NOW Function returns the current date and time − formatted as date and time − as shown below in cell B1. If the cell format was General before using the function, Excel will change the cell format to match your regional settings for date and time. The NOW Function has no arguments, but the […]

Get the full story …

## Convert Seconds to a Time Format in Excel

August 26, 2010 Formulas

The last several posts have shown how to convert and aggregate a given number of seconds that represent song duration. A simple example because song duration never exceeded one hour. But what if your data has values greater than 3600 seconds? How would you convert those seconds into an hour:minute:second format? Luckily, the previously mentioned […]

Get the full story …

## Using the SUM Function for a Running Total in Excel

August 24, 2010 Beginner

I’ve exported an iTunes playlist that shows the number of seconds per song and converted those seconds to a minutes:seconds format as you can see in column N in the spreadsheet below. I now want a running summary in column O to see the total time after each song. The SUM Function works perfectly when […]

Get the full story …

## Putting the TIME, INT and MOD Functions to Work in Excel

August 21, 2010 Formulas

Converting seconds into a minutes:seconds format is rather simple once you know the component parts of the formula. We’ll use three functions. (click to review prior post) TIME(hour,minute,second) INT(number) MOD(number,divisor) For the TIME Function, the three arguments are: hour = 0 minute = INT(number) second = MOD(number, divisor) Which leaves us with: TIME(0, INT(number), MOD(number,divisor)) […]

Get the full story …

## The TIME Function in Excel

August 19, 2010 Formulas

I’ve been looking at converting a number of seconds into a minutes:seconds format. My example is converting 274 seconds by using the INT Function to extract 4 minutes and the MOD Function to extract 34 seconds. In this post I’m using those values inside the TIME Function. The Time Function has three arguments: Hour, Minute, […]

Get the full story …

## The MOD Function in Excel

August 17, 2010 Formulas

The MOD Function simply returns a remainder, or fractional part, after a number has been divided. To understand the MOD Function better, a look at how the INT Function can be used may provide some insight. First, the calculation example is 274 seconds converted to 4:34.  The INT Function was used in a previous post, […]

Get the full story …

## QUOTIENT, ROUNDDOWN and INT Functions in Excel

August 14, 2010 Formulas

My task is to take a quantity of seconds and convert to a minutes:seconds format. For example, I need 274 seconds to become 4:34 in my spreadsheet. The first step, which I’ll cover in this post, is to get the number of minutes. As I’ve mentioned before, three Functions will do this for me: QUOTIENT, […]

Get the full story …

## iTunes Playlist Export Task – Convert Seconds to a Time Format

August 12, 2010 Formulas

I recently exported an iPod playlist to check the total duration of time for all songs, but found the unit of time to be in seconds. For example, 274 seconds is not a useful time format for song duration. Converting this to a minutes:seconds format is my task. Getting minutes is easy, just divide 274 […]

Get the full story …

## Convert a Text String Date to Date Serial Number

August 10, 2010 Formulas

In previous posts I’ve been going through some formulas designed to extract and convert a text string Date, to a Date serial number. Extract a Date from Text in Excel The MID Function in Excel The RIGHT Function in Excel CONCATENATE Function or Ampersand Operator in Excel The TYPE Function in Excel The VALUE Function […]

Get the full story …