Results 1 to 12 of 12

20080711, 23:56 #1
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
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

20080712, 00:09 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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+1E8,H8E8)(G8F8)+IF(J8<I8,J8+1J8,J8I8)),0)/24
If all users have the Analysis ToolPak addin installed, you can also use the MROUND function:
=MROUND(IF(H8<E8,H8+1E8,H8E8)(G8F8)+IF(J8<I8,J8+1J8,J8I8),1/24)
3) When I enter some data, the weekly hours appear to be calculated correctly.

20080712, 01:06 #3
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
Re: Formating Times and Calculating Time (2007)
Hi Hans
Thanks for the reply.
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 hourwhat 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

20080712, 01:11 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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+1E8,H8E8)(G8F8)+IF(J8<I8,J8+1J8,J8I8))*24
And then format as a number so there is no conversion to a date or a time...
Steve

20080712, 01:22 #5
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
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

20080712, 01:38 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20080712, 02:05 #7
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
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

20080712, 02:08 #8
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>

20080712, 02:16 #9
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
Re: Formating Times and Calculating Time (2007)
Hi Vegasnath
Thanks for the help. It works perfectly
Regards
Howard

20080712, 05:09 #10
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
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

20080712, 05:16 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20080712, 05:40 #12
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
Re: Formating Times and Calculating Time (2007)
Hi Hans
Thanks for the help. Muxch appreciated
Regards
Howard