Date & time calculations and I/O
Do you know of a way to skip holidays on a date calculation?
Use NETWORKDAYS(start_date,end_date,holidays)
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
Does SpreadsheetConverter support date calculations?
Yes.
You can see an example below, were we build the date using Excel's date function and substract the values.
http://www.spreadsheetconverter.com/manual/date_dropdown_lis t.htm
SpreadsheetConverter will convert the date calculations into JavaScript.
I have a dropdown list with dates but SpreadsheetConverter shows 37000,37001,... ?
SpreadsheetConverter cannot handle dates in dropdown lists.
Instead use DATE to build a date. See
http://www.spreadsheetconverter.com/manual/date_dropdown_lis t.htm
What is your recommendation for TIME data entry -- e.g. time in and time out?
Use 24 hour clock, or if 12 hour clock is very important, use 2 fields one for 0-11 and a dropdown list for AM/PM and use TIME().
Or you can use TIME to build time values, for an example see
http://www.spreadsheetconverter.com/manual/date_dropdown_lis t.htm
To output date, use date formats, for example 'hh:mm'
There is also a problem with date formatting. In excel I asigned a date format 31-12-2009 and after conversion the asp-file shows 31/12/2009. Why?
We use the same format for dates as in regional settings. However, there might be issues. In the worst case, you might to have to change your input method and to like this:
http://www.spreadsheetconverter.com/samples/time_date/time_d ate.htm
