Tuesday, August 16, 2005

Date and Time Formats



Wolfgang asked
hi gents (and ladies)...

the next-door comedian (co-worker) just asked me if he could add hours to the following fancy format and no formulas please:

a1 = mon 08:00
b1 = 8.5
c1 = mon 16:30

he has a table with some 90 rows which he need to sum up and of course, all possible day combinations included, mon to sun...

i told him that nobody has time enough to code this...am i right?!

best,
wolfgang
I replied
Hi wolfgang

I'm not 100% sure what you want but I assume that your coworker wants to be able to allow users to enter the starting time and hours worked and calculate a closing time. This needs to be displayed in the format as you wrote it.

The easiest way to do this is to format the cells themselves to handle dates and hours. Go to Format Cells Number and select Custom to do this.
The first and last cells (A1 and C1) need to have a custom format of ddd hh:mm. Cell B1 needs a format of h:mm.
This will give you Mon 08:00 when you enter that date and time in - this is easily editable in the formula bar or can be entered as 15/7/05 09:00 for 9am on Friday 15th August.
The second cell B1 needs to have the date entered by typing 08:30 as in the hours and minutes required separated by a colon and needs the leading zero
The third cell C1 can then just have a simple formula =A1+B1 and it will calculate the ending time.

To force the users to enter the data in the correct format you can use Data Validation to require the format and limit the times that can be entered.
Select B1 and click Data Validation on the menu
Select Time in the Allow box
Choose between 0:00 and 23:59 or any other times required
Yoiu can use the Input Message and Error Alert tabs to modify what the user sees if they enter incorrectly.
Repeat for A1 and if necessary lock the spreadsheet only allowing cells A1 and B1 to be entered into. Do this by formatting the cells and going to the Protection Tab and turning off Locked. Then go to Tools Protection Protect Sheet to protect the sheet. Use this sparingly and only when you can't trust the users not to muck it up by typing over formulas.

You can prepare a whole sheet by selecting the whole column before editing the format
Hope this answers the question.


Posted by: Jethro | 9:43 PM |