Solved

# Excel DateTime

Posted on 2012-08-18
574 Views
I have a field that is date and time.  I want it to include seconds in the time?  How can I make this happen?   So I can get sales between to periods down to the seconds.  Thanks!
0
Question by:evend

LVL 24

Expert Comment

does the cell with the time in it already have a second portion
i.e. if you change the format of the cell to custom dd/mm/yy hh:nn:ss does it have seconds?
An example file would always be handy :)
0

LVL 33

Expert Comment

How are you entering the date/times?
0

Author Comment

Well let me step back. I have a date column and a transaction time column with seconds.  I converted to one field using =INT(J2)+MOD(N2,1).  I would like one field with date and time down to the seconds.  I hope this helps.
0

LVL 33

Accepted Solution

Just add the two columns like this,

=J2+N2

then you'll keep the time part including seconds.
0

LVL 81

Expert Comment

Here are some notes about how Excel stores dates and times. This should help you in the future when working with them.

Excel stores dates and times as real numbers with an integer part and a fractional part. The integer part is the number of days since December 31, 1899. The fractional part is the time of the day represented by the integer part. So 12:00 AM on January 1, 1900 is 1.0 and 12:00 PM on January 1, 1900 is 1.5. 6:00 PM on January 1, 2005 is 38,353.75. When a correctly formatted date and/or time is entered into a cell, Excel automatically converts the value entered into a date-time value. Sometimes just a time is required. If 3 PM is entered without a date then Excel assumes the date part is 0 so the value stored is 0.625 (12 + 3)/24. In the remainder of this discussion the term date or date value is used to refer to any value stored as a date-time value.

Date and Time Math

Since dates and times are stored this way date math becomes very simple. For example, to find the number of days between two dates you can subtract one from the other: February 10, 2005 - January 25, 2005 = 38,377.00 - 38,393.00 or 16 days. Dates with times can be manipulated the same way: The difference in days between 6:00 AM on February 2, 2005 and 12:00 PM on January 31, 2005 is 38,385.25 - 38,383.50 or 1.75 days. 1.75 days is really 1 day and 18 hours where 18 is ¾ or .75 of one day.

Care must be taken when doing math with dates containing just times. For example, one might think that if a start time of 9 PM is subtracted from a stop time of 3 AM the result will be 0.25 or 6 hours but the actual result is -0.75 or -18 hours. This is because without a date part the times are just fractions of a day (positive values between 0 and 1) and Excel does not know to which day they belong. For example:

3 AM - 9 PM = 0.1250 - 0.8750 = -0.75 = -18 hours

This is easily fixed by adding 1 to the 3 AM date value because it is actually one day in the future:

3 AM + 1 - 9 PM = 0.1250 + 1 - 0.8750 = 1.1250 - 0.8750 = 0.25 = 6 hours

If it is not known whether or not the stop time will be the next day but the difference between the two times will never be more than 24 hours then an IF clause can be used:

HoursWorked = IF(StopTime <= StartTime, StopTime + 1 - StartTime, StopTime - StartTime)

A shorter formula that accomplishes the same goal is:

HoursWorked = MOD(StopTime - StartTime, 1)

Calculating Hours, Minutes, and Seconds

Given any date which is entered as a date and/or time value, or is the result of date time math, the number of hours, minutes, or seconds represented by that date can be determined with some formulas. For example, given the result of the math above, 1.75 or 1 day and 18 hours, the total number of hours represented by that date value is calculated by multiplying by 24:

= 1.75 * 24 = 42

The number of minutes is calculated by multiplying by 24 * 60:

= 1.75 * 24 * 60 = 2,520

The number of seconds is calculated by multiplying by 24 * 60 * 60:

= 1.75 * 24 * 60 * 60 = 151,200

Kevin
0

LVL 1

Expert Comment

Can you please expand upon your desired result of "sales between 2 periods down to seconds"?
0

LVL 50

Expert Comment

Hello,

if you want to work with date/time values to include seconds, then don't strip the decimal points off the values with Int() or Mod().  A date/time value can have quite a few decimals, which is exactly where minutes and seconds are stored.

Then just do the maths of subtracting one value from the other.

The rest is just formatting. To display a result as the number of seconds, use custom format

[ss]

To show the total number of minutes and the seconds, use [mm]:ss
To show the total number of hours, minutes and seconds, use [hh]:mm:ss
To show the number of days, then hours, minutes and seconds, use d mm:hh:ss

The variations are numerous. See attached.

That is for displaying the time difference.

If you want to count values that fall into a specific time frame, including the seconds, then you can use something like

=COUNTIFS(B1:B100,">"&DATE(2012,8,18)+TIME(20,0,0),B1:B100,"<"&DATE(2012,8,20)+TIME(8,0,0))

Or, if the dates and times are stored in cells, just refer to the cells

=COUNTIFS(B1:B100,">"&A1+A2,B1:B100,"<"&A3+A4)

where A1 and A3 are date values, A2 and A4 are time values.

cheers, teylyn
27833728.xlsx
0

## Featured Post

### Suggested Solutions

Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.