Date and Time Format?

[JPLoos]

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?

[Tom Schreiner]

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

[Yogi Anand]

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

[WayneTN]

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 ]

[Yogi Anand]

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")

[WayneTN]

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). :->

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