How do I import excel time values to lotus notes

I have a spreadsheet with various data and  time values, i.e. - 6:00 pm. I save the excel file as a 1-2-3 wks file and import into a Lotus Notes DB. Everything imports fine except for the times. Are there any tricks to this?
jforget1Asked:
Who is Participating?
 
leonstrykerCommented:
You can try converting the dates into text format before export. Or save the file as CSV and then import into Lotus Notes
0
 
leonstrykerCommented:
Are there any tricks to this?

What format does it expect teh data to be in? You may then modify it in Excel before exporting.
0
 
jforget1Author Commented:
Lotus note is expecting "6:00 PM" format, however when converting to 1-2-3 wks format, it changes to a decimal, i.e. - .0125. If I modify the cell back to "6:00 PM" it says the data is not compatible with the wks file format.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Sjef BosmanGroupware ConsultantCommented:
Maybe it's time to start writing an agent using COM, to interface directly with Excel?
0
 
Sjef BosmanGroupware ConsultantCommented:
Didn't work?? Did you even try with a COM-agent? Here's an example:

http://www.botstation.com/code/excel2notes.php
0
 
jforget1Author Commented:
Tried the COM-agent, and I still get the weird decimal conversion for the time values.
0
 
jforget1Author Commented:
Please leave question open for additional suggestions
0
 
jforget1Author Commented:
I just tried changing the Time field to text and attempted the import, that also produces the decimal conversion
0
 
Sjef BosmanGroupware ConsultantCommented:
The time field should be a time field in Excel, and then try to import it as a Variant.

You could also do the inverse, in order to find out what type the field should be. Write a small agent that creates or updates an Excel file, putting a date in a field. Plenty examples of Export agents on the net as well, here's one:
- http://www.botstation.com/code/view2exceladv.php
0
 
jforget1Author Commented:
I have the field in Notes set to time. I just figured part of it out though, on the excel sheet, I changed the column from a time format to a text format and got the funky decimal conversion. So I deleted a couple of cells data and hand entered the time data (10:00 PM). The import worked perfectly on the cells I changed.

Now I need to find a way to convert the time columns to text columns without the funky decimal conversion, aside from doing it by hand.
0
 
Sjef BosmanGroupware ConsultantCommented:
So if it is a real date/time field it works. Good. Progress. Love it :)

Maybe try DateValue() ?

See:
- http://support.microsoft.com/kb/214094
0
 
jforget1Author Commented:
The suggestions aided in the solution, however no one suggestion was the solution.
0
 
Sjef BosmanGroupware ConsultantCommented:
Was there a solution after all??
0
 
jforget1Author Commented:
I ended up having to modify the spreadsheet times to a text format using the text to columns function, then using a COM-Agent like you suggested, imported the data without issue.
0
 
Sjef BosmanGroupware ConsultantCommented:
Ok, thanks :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.