Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

Excel DateTime

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
evend
Asked:
evend
1 Solution
 
SteveCommented:
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
 
NorieData ProcessorCommented:
How are you entering the date/times?
0
 
evendAuthor Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
NorieData ProcessorCommented:
Just add the two columns like this,

=J2+N2

 then you'll keep the time part including seconds.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
nombplumCommented:
Can you please expand upon your desired result of "sales between 2 periods down to seconds"?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now