# Thread: Formating Times and Calculating Time (2007)

1. ## Formating Times and Calculating Time (2007)

Attached please find a Time Sheet

I need the following

1) To allow users to enter the times for eg 08.00 , 08.15, 13.15 or 0800 0815 1315 and this must then reflect as 8am, 8.15am 13.15am
2) To calculate the total hours as a whole number
3) A formula to calculate the total hours -I have tried to set up a formula, but it is not working correctly

Your assistance will be most appreciated

Howard

2. ## Re: Formating Times and Calculating Time (2007)

1) See the thread starting at <post:=719,975>post 719,975</post:> for various suggestions on how to enter times.
2) You can use the following formula in K8 to round hours worked to the nearrest hour:

=ROUND(24*(IF(H8<E8,H8+1-E8,H8-E8)-(G8-F8)+IF(J8<I8,J8+1-J8,J8-I8)),0)/24

If all users have the Analysis ToolPak add-in installed, you can also use the MROUND function:

=MROUND(IF(H8<E8,H8+1-E8,H8-E8)-(G8-F8)+IF(J8<I8,J8+1-J8,J8-I8),1/24)

3) When I enter some data, the weekly hours appear to be calculated correctly.

3. ## Re: Formating Times and Calculating Time (2007)

Hi Hans

1) I tried to use the codes/suggestions in the post 719,975 to format times , but without any success

2) I did not want the time rounded to the nearest hour-what i was looking for what the time to be shown as as whole number for eg 7:30 hours to be shown in the total hours as 7.5 as it is seven & a half hours 7:15 to be shown as 7.25 as it is actually seven and a quarter hours etc

Your assistance in this regard will be most appreciated

Regards

Howard

4. ## Re: Formating Times and Calculating Time (2007)

Excel stores dates and times in units of days, so multiply the days by 24:
=(IF(H8<E8,H8+1-E8,H8-E8)-(G8-F8)+IF(J8<I8,J8+1-J8,J8-I8))*24

And then format as a number so there is no conversion to a date or a time...

Steve

5. ## Re: Formating Times and Calculating Time (2007)

Hi Steve

Thanks for the help. If all the field are completed formula calculates Hours worked correctly. However, if for eg cell E8 & E9 is completed, then the Hours worked is incorrect.

It would be appreciated if you could assist in this regard.

Regards

Howard

6. ## Re: Formating Times and Calculating Time (2007)

The attached version only calculates worked hours if all fields have been entered.
The worksheet module contains code derived from Date And Time Entry by Chip Pearson to let the user enter times as 800 or 1630 for 8:00 AM and 4:30 PM.
The weekly hours have also been formatted as numbers instead of as times.

7. ## Re: Formating Times and Calculating Time (2007)

Hi Hans

Thanks for the reply. I have now managed to sort out the formatting of the times per the VBA code developed by Chip Pearson http://www.cpearson.com/excel/DateTimeEntry.htm

It would be appreciated if you would assist in modifying my formula that if only certain of the fields are completed for eg E8 & F8 the correct time worked will be calculated or if time is entered into the starting time only , then a zero will be calculated into Hours worked

Your assistance will be most appreciated

Regards

Howard

8. ## Re: Formating Times and Calculating Time (2007)

Whilst Hans version works, Mr Rayners may not be too pleased if he does not get paid for part day's.

The attached version calculates, even if Mr Rayners forgets to return from his tea break. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

9. ## Re: Formating Times and Calculating Time (2007)

Hi Vegasnath

Thanks for the help. It works perfectly

Regards

Howard

10. ## Re: Formating Times and Calculating Time (2007)

Hi Hans

The code written by Chip Pearson is great, except the it does not cater for times entered as 2 digits for eg 10, 12, 15 etc if for eg 10 is entered it appears as 12:10 if 12 is entered it appears as 12:12

It would be appreciated if you would amend the cater to cater for this

Regards

Howard

11. ## Re: Formating Times and Calculating Time (2007)

You can combine Case 1 and Case 2 in the Select Case block as follows:
<code>
Case 1, 2 ' e.g., 10 = 10:00 AM
TimeStr = .Value & ":00"
</code>
As a consequence, you won't be able to enter a time such as 12:10 AM by typing 10 or 010 or 0010. You'll have to enter 0:10 and click OK in the error message box.

12. ## Re: Formating Times and Calculating Time (2007)

Hi Hans

Thanks for the help. Muxch appreciated

Regards

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•