Date and Time Format?

JPLoos

New Member
Joined
May 11, 2002
Messages
1
I am gathering date and time data in XML. Then dropping the date and time information into excel so my co-workers can easily manipulate the data. However when I drop the date and time information into excel the seconds is truncated. For example 05122002 03:12:32 becomes 5/12/2002 3:12 NO Seconds? Is there an after market formating patch that enables Excel to deal with seconds?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I think that is the default format for the date and time you are entering. Did you try and format the cells to include seconds?
The full time should show seconds in your formula bar. If the seconds are correct in the formula bar then you can show them by formatting the cell correctly.
Tom
 
Upvote 0
On 2002-05-12 20:37, JPLoos wrote:
I am gathering date and time data in XML. Then dropping the date and time information into excel so my co-workers can easily manipulate the data. However when I drop the date and time information into excel the seconds is truncated. For example 05122002 03:12:32 becomes 5/12/2002 3:12 NO Seconds? Is there an after market formating patch that enables Excel to deal with seconds?

As Tom said in his response, all you have to do is have the right formatting.
In this case you would need to Custom Format the entry as

m/dd/yyyy h:mm:ss
 
Upvote 0
I agree that the correct formatting should solve your problem. However, I recently encountered a similar situation where I needed to know how many days (an exact whole number) it had been since the workbook just opened had been updated (modified). I just simply truncated the minutes and seconds off of the last modification date & time and then subtracted that date from today's date. This gave me a whole number of days since the last mod. This way I avoided fractions of days.
This message was edited by WayneTN on 2002-05-13 09:06
 
Upvote 0
On 2002-05-13 09:05, WayneTN wrote:
I agree that the correct formatting should solve your problem. However, I recently encountered a similar situation where I needed to know how many days (an exact whole number) it had been since the workbook just opened had been updated (modified). I just simply truncated the minutes and seconds off of the last modification date & time and then subtracted that date from today's date. This gave me a whole number of days since the last mod. This way I avoided fractions of days.
This message was edited by WayneTN on 2002-05-13 09:06

Hi WayneTN:

The date format shows composite date and time. The days only part can be extracted using the INT function ...

=INT(A1-B1) ... will give you just the whole days and drop the fractional time element.

If you wanted to pick up the number of full days and fractional part, the hour, minutes and seconds also, you can use the following formula:

=INT(A1-B1)&"Days and "&TEXT(MOD((A1-B1),1),"[h]:mm:ss")
 
Upvote 0
Thanks for the explanation. I must correct some wording I used above, however. Upon further review, I didn't technically "truncate" the date/time, I rounded down the LastMod date/time to give me the equivalent of a whole day. The exact code that I used, shown below, was adapted from a related, 4/15/02 post by Ivan Moala (thanks Ivan). :->

<pre>
Function LastMod()
'//Finds the File's last modified date/time
'//...display depends on cell date/time format
'//Use: =ROUNDDOWN(LastMod(),0)
'//Returns: mm/dd/yy with minutes & seconds at zero
'//...so exact no. of days since last mod can be calc.

Dim Fso, F
Set Fso = CreateObject("Scripting.FileSystemObject")
Set F = Fso.GetFile("C:\yourfilename.xls")
LastMod = F.DateLastModified
</pre>
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top