?
Solved

How do I import excel time values to lotus notes

Posted on 2011-04-28
15
Medium Priority
?
608 Views
Last Modified: 2013-12-18
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?
0
Comment
Question by:jforget1
  • 7
  • 6
  • 2
15 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 35486918
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
 

Author Comment

by:jforget1
ID: 35487064
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 35487146
Maybe it's time to start writing an agent using COM, to interface directly with Excel?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 29

Accepted Solution

by:
leonstryker earned 750 total points
ID: 35487242
You can try converting the dates into text format before export. Or save the file as CSV and then import into Lotus Notes
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 750 total points
ID: 35513581
Didn't work?? Did you even try with a COM-agent? Here's an example:

http://www.botstation.com/code/excel2notes.php
0
 

Author Comment

by:jforget1
ID: 35692943
Tried the COM-agent, and I still get the weird decimal conversion for the time values.
0
 

Author Comment

by:jforget1
ID: 35693125
Please leave question open for additional suggestions
0
 

Author Comment

by:jforget1
ID: 35693135
I just tried changing the Time field to text and attempted the import, that also produces the decimal conversion
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 35695984
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
 

Author Comment

by:jforget1
ID: 35698267
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 35702586
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
 

Author Closing Comment

by:jforget1
ID: 35732066
The suggestions aided in the solution, however no one suggestion was the solution.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 35733295
Was there a solution after all??
0
 

Author Comment

by:jforget1
ID: 35739767
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 35739834
Ok, thanks :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question