Time calculations

I have a simple spreadsheet for determining the hours and minutes that an 
employee works during a pay period.  The current result is formatted like 
"38:45"  meaning 38 hours and 45 minutes.  My payroll software needs the 
time worked in decimal format (i.e., 38.75).

Can someone please show me how I would use an Excel formula to covert hours 
and minutes to hours and fraction of hours.  Thank you.



-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey -  Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~



0
gmaxey (30)
3/19/2008 4:52:52 AM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
630 Views

Similar Articles

[PageSpeed] 46

=A1*24 and format as General.

If your app absolutely requires two and only two decimal digits, then:

Format > Cells... > Number > Number > and specify 2 digits
-- 
Gary''s Student - gsnu2007g


"Greg Maxey" wrote:

> I have a simple spreadsheet for determining the hours and minutes that an 
> employee works during a pay period.  The current result is formatted like 
> "38:45"  meaning 38 hours and 45 minutes.  My payroll software needs the 
> time worked in decimal format (i.e., 38.75).
> 
> Can someone please show me how I would use an Excel formula to covert hours 
> and minutes to hours and fraction of hours.  Thank you.
> 
> 
> 
> -- 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Greg Maxey -  Word MVP
> 
> My web site http://gregmaxey.mvps.org
> Word MVP web site http://word.mvps.org
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 
> 
> 
> 
0
GarysStudent (1572)
3/19/2008 9:08:01 AM
Gary,

Thanks.  This works.  I saw some odd behaviour at first but it seems to be 
spot on now.  This is how my spreadsheet is laid out:

I have four columns labeled:

In      Out      Lunch      Total


Each following cell the four columns is formatted for time.  I enter the 
start time like  07:05  an end time like 16:15   a standard 0:30 for lunch 
and column D has a forumla:
=(B2-A2-C2) which gives a total time in hours and minutes.

I have six rows laid out similiar to the above (Mon - Sat).  In Cell D8 I 
nave teh forumual =(SUM(D2:D7)).  This gives me the time in hours and 
minutes.

I placed the formula that you provided in Cell D9.  I formatted it as 
"General" and then "Custom" "0.00"

The first time I tried this the odd behaviour was showing up in D8.  I 
entered 08:00 in the first five "In" fields and "16:30" in the first 5 "Out" 
fields.  I expected to see "40:00" in D8 and "40.00" in D9.  What I saw was 
"16:00" in D8 and "40.00" in D9.  I tried a few changes and each time the D9 
value was correct but the D8 value was low.  Finally I set all the value to 
0 and started over.  It worked perfectly.

Any idea what caused the behaviour described above?  I can't imagine how the 
application gets "40.00" out of "16:00 * 24"  then again I don't understand 
how it gets "40.00" out of "40:00 * 24" either.

A few more questions if I may.

1.  I would like to "reset" the time in and time out values to 08:00 - 16:00 
after I compute the totals for each employee.  I know nothing about Excel 
VBA but I know enough about Word VBA to be certain that it could be done.  I 
suppose the code would look something like:

For Each oCell in oRng.Cells
  oCell.Value = "0:00"
Next oCell

2.  Is there a way to autoformat the text entry?  It would be much more 
efficient if I could enter 0700 (without the colon) and the cell content 
autoformat to 07:00.

Thanks again.




-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey -  Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Gary''s Student wrote:
> =A1*24 and format as General.
>
> If your app absolutely requires two and only two decimal digits, then:
>
> Format > Cells... > Number > Number > and specify 2 digits
>
>> I have a simple spreadsheet for determining the hours and minutes
>> that an employee works during a pay period.  The current result is
>> formatted like "38:45"  meaning 38 hours and 45 minutes.  My payroll
>> software needs the time worked in decimal format (i.e., 38.75).
>>
>> Can someone please show me how I would use an Excel formula to
>> covert hours and minutes to hours and fraction of hours.  Thank you.
>>
>>
>>
>> --
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Greg Maxey -  Word MVP
>>
>> My web site http://gregmaxey.mvps.org
>> Word MVP web site http://word.mvps.org
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 


0
gmaxey (30)
3/19/2008 11:57:04 AM
With regard to resetting values to 8:00 and 16:00, try this code :

Sub time_reset()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To n
    Cells(i, 1) = #8:00:00 AM#
    Cells(i, 2) = #4:00:00 PM#
Next
End Sub

It simply fills the used portions of columns A & B with the desired reset 
values.


Starting with an empty column that has been formatted to Text, enter values 
like:
0534
0811
1130
without any colons and then select the cells and then run:

Sub time_converter()
For Each r In Selection
    v = r.Value
    hrs = Left(v, 2)
    mins = Right(v, 2)
    r.Value = TimeSerial(hrs, mins, 0)
    r.NumberFormat = "h:mm;@"
Next
End Sub
-- 
Gary''s Student - gsnu200774


"Greg Maxey" wrote:

> Gary,
> 
> Thanks.  This works.  I saw some odd behaviour at first but it seems to be 
> spot on now.  This is how my spreadsheet is laid out:
> 
> I have four columns labeled:
> 
> In      Out      Lunch      Total
> 
> 
> Each following cell the four columns is formatted for time.  I enter the 
> start time like  07:05  an end time like 16:15   a standard 0:30 for lunch 
> and column D has a forumla:
> =(B2-A2-C2) which gives a total time in hours and minutes.
> 
> I have six rows laid out similiar to the above (Mon - Sat).  In Cell D8 I 
> nave teh forumual =(SUM(D2:D7)).  This gives me the time in hours and 
> minutes.
> 
> I placed the formula that you provided in Cell D9.  I formatted it as 
> "General" and then "Custom" "0.00"
> 
> The first time I tried this the odd behaviour was showing up in D8.  I 
> entered 08:00 in the first five "In" fields and "16:30" in the first 5 "Out" 
> fields.  I expected to see "40:00" in D8 and "40.00" in D9.  What I saw was 
> "16:00" in D8 and "40.00" in D9.  I tried a few changes and each time the D9 
> value was correct but the D8 value was low.  Finally I set all the value to 
> 0 and started over.  It worked perfectly.
> 
> Any idea what caused the behaviour described above?  I can't imagine how the 
> application gets "40.00" out of "16:00 * 24"  then again I don't understand 
> how it gets "40.00" out of "40:00 * 24" either.
> 
> A few more questions if I may.
> 
> 1.  I would like to "reset" the time in and time out values to 08:00 - 16:00 
> after I compute the totals for each employee.  I know nothing about Excel 
> VBA but I know enough about Word VBA to be certain that it could be done.  I 
> suppose the code would look something like:
> 
> For Each oCell in oRng.Cells
>   oCell.Value = "0:00"
> Next oCell
> 
> 2.  Is there a way to autoformat the text entry?  It would be much more 
> efficient if I could enter 0700 (without the colon) and the cell content 
> autoformat to 07:00.
> 
> Thanks again.
> 
> 
> 
> 
> -- 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Greg Maxey -  Word MVP
> 
> My web site http://gregmaxey.mvps.org
> Word MVP web site http://word.mvps.org
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 
> 
> 
> Gary''s Student wrote:
> > =A1*24 and format as General.
> >
> > If your app absolutely requires two and only two decimal digits, then:
> >
> > Format > Cells... > Number > Number > and specify 2 digits
> >
> >> I have a simple spreadsheet for determining the hours and minutes
> >> that an employee works during a pay period.  The current result is
> >> formatted like "38:45"  meaning 38 hours and 45 minutes.  My payroll
> >> software needs the time worked in decimal format (i.e., 38.75).
> >>
> >> Can someone please show me how I would use an Excel formula to
> >> covert hours and minutes to hours and fraction of hours.  Thank you.
> >>
> >>
> >>
> >> --
> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >> Greg Maxey -  Word MVP
> >>
> >> My web site http://gregmaxey.mvps.org
> >> Word MVP web site http://word.mvps.org
> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
> 
> 
> 
0
GarysStudent (1572)
3/19/2008 2:31:01 PM
Gary,

Thanks for the code.  I modified is slightly so that it doesn't effect the 
row for Saturday which is normally not a workday and set at 0:00.  It works 
perfectly.

However, I am still seeing the odd value in Cell D8.  When I reset the 
values D8 reads "16:00" while D9 reads "40:00"

Again. D8 uses a formula =Sum(D2:D7) and the formula in D9 is =(D8*24).

The value in D2, D3, D4, D5, and D6 is "8:00" the value in D7 is "0"00"

How can D8 read "16:00" if is the sum of 8:00+8:00+8:00+8:00+8:00  ??

How can D9 read "40:00" if it is the sum of 16:00*24  ??



-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey -  Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~


"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
news:E825CF3C-D954-4182-B5BF-5BA1C72D0B5C@microsoft.com...
> With regard to resetting values to 8:00 and 16:00, try this code :
>
> Sub time_reset()
> n = Cells(Rows.Count, 1).End(xlUp).Row
> For i = 2 To n
>    Cells(i, 1) = #8:00:00 AM#
>    Cells(i, 2) = #4:00:00 PM#
> Next
> End Sub
>
> It simply fills the used portions of columns A & B with the desired reset
> values.
>
>
> Starting with an empty column that has been formatted to Text, enter 
> values
> like:
> 0534
> 0811
> 1130
> without any colons and then select the cells and then run:
>
> Sub time_converter()
> For Each r In Selection
>    v = r.Value
>    hrs = Left(v, 2)
>    mins = Right(v, 2)
>    r.Value = TimeSerial(hrs, mins, 0)
>    r.NumberFormat = "h:mm;@"
> Next
> End Sub
> -- 
> Gary''s Student - gsnu200774
>
>
> "Greg Maxey" wrote:
>
>> Gary,
>>
>> Thanks.  This works.  I saw some odd behaviour at first but it seems to 
>> be
>> spot on now.  This is how my spreadsheet is laid out:
>>
>> I have four columns labeled:
>>
>> In      Out      Lunch      Total
>>
>>
>> Each following cell the four columns is formatted for time.  I enter the
>> start time like  07:05  an end time like 16:15   a standard 0:30 for 
>> lunch
>> and column D has a forumla:
>> =(B2-A2-C2) which gives a total time in hours and minutes.
>>
>> I have six rows laid out similiar to the above (Mon - Sat).  In Cell D8 I
>> nave teh forumual =(SUM(D2:D7)).  This gives me the time in hours and
>> minutes.
>>
>> I placed the formula that you provided in Cell D9.  I formatted it as
>> "General" and then "Custom" "0.00"
>>
>> The first time I tried this the odd behaviour was showing up in D8.  I
>> entered 08:00 in the first five "In" fields and "16:30" in the first 5 
>> "Out"
>> fields.  I expected to see "40:00" in D8 and "40.00" in D9.  What I saw 
>> was
>> "16:00" in D8 and "40.00" in D9.  I tried a few changes and each time the 
>> D9
>> value was correct but the D8 value was low.  Finally I set all the value 
>> to
>> 0 and started over.  It worked perfectly.
>>
>> Any idea what caused the behaviour described above?  I can't imagine how 
>> the
>> application gets "40.00" out of "16:00 * 24"  then again I don't 
>> understand
>> how it gets "40.00" out of "40:00 * 24" either.
>>
>> A few more questions if I may.
>>
>> 1.  I would like to "reset" the time in and time out values to 08:00 - 
>> 16:00
>> after I compute the totals for each employee.  I know nothing about Excel
>> VBA but I know enough about Word VBA to be certain that it could be done. 
>> I
>> suppose the code would look something like:
>>
>> For Each oCell in oRng.Cells
>>   oCell.Value = "0:00"
>> Next oCell
>>
>> 2.  Is there a way to autoformat the text entry?  It would be much more
>> efficient if I could enter 0700 (without the colon) and the cell content
>> autoformat to 07:00.
>>
>> Thanks again.
>>
>>
>>
>>
>> -- 
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Greg Maxey -  Word MVP
>>
>> My web site http://gregmaxey.mvps.org
>> Word MVP web site http://word.mvps.org
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>>
>>
>> Gary''s Student wrote:
>> > =A1*24 and format as General.
>> >
>> > If your app absolutely requires two and only two decimal digits, then:
>> >
>> > Format > Cells... > Number > Number > and specify 2 digits
>> >
>> >> I have a simple spreadsheet for determining the hours and minutes
>> >> that an employee works during a pay period.  The current result is
>> >> formatted like "38:45"  meaning 38 hours and 45 minutes.  My payroll
>> >> software needs the time worked in decimal format (i.e., 38.75).
>> >>
>> >> Can someone please show me how I would use an Excel formula to
>> >> covert hours and minutes to hours and fraction of hours.  Thank you.
>> >>
>> >>
>> >>
>> >> --
>> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> >> Greg Maxey -  Word MVP
>> >>
>> >> My web site http://gregmaxey.mvps.org
>> >> Word MVP web site http://word.mvps.org
>> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>>
>> 


0
gmaxey (30)
3/19/2008 9:32:46 PM
There are several things to check:

1. compare the formats of the cells that are correct to the cells (like D8) 
that are not correct.  It might just  a formatting issue.

2. Insure that D2 thru D7 are genuine numbers.  SUM() ignores non-numbers 
and won't even tell you.

If worst come to worst, select D2 thru D8 and change the format to General.  
It will then be easier to check the math!!

Update this post if problem persist.
-- 
Gary''s Student - gsnu200774


"Greg Maxey" wrote:

> Gary,
> 
> Thanks for the code.  I modified is slightly so that it doesn't effect the 
> row for Saturday which is normally not a workday and set at 0:00.  It works 
> perfectly.
> 
> However, I am still seeing the odd value in Cell D8.  When I reset the 
> values D8 reads "16:00" while D9 reads "40:00"
> 
> Again. D8 uses a formula =Sum(D2:D7) and the formula in D9 is =(D8*24).
> 
> The value in D2, D3, D4, D5, and D6 is "8:00" the value in D7 is "0"00"
> 
> How can D8 read "16:00" if is the sum of 8:00+8:00+8:00+8:00+8:00  ??
> 
> How can D9 read "40:00" if it is the sum of 16:00*24  ??
> 
> 
> 
> -- 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Greg Maxey -  Word MVP
> 
> My web site http://gregmaxey.mvps.org
> Word MVP web site http://word.mvps.org
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 
> 
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
> news:E825CF3C-D954-4182-B5BF-5BA1C72D0B5C@microsoft.com...
> > With regard to resetting values to 8:00 and 16:00, try this code :
> >
> > Sub time_reset()
> > n = Cells(Rows.Count, 1).End(xlUp).Row
> > For i = 2 To n
> >    Cells(i, 1) = #8:00:00 AM#
> >    Cells(i, 2) = #4:00:00 PM#
> > Next
> > End Sub
> >
> > It simply fills the used portions of columns A & B with the desired reset
> > values.
> >
> >
> > Starting with an empty column that has been formatted to Text, enter 
> > values
> > like:
> > 0534
> > 0811
> > 1130
> > without any colons and then select the cells and then run:
> >
> > Sub time_converter()
> > For Each r In Selection
> >    v = r.Value
> >    hrs = Left(v, 2)
> >    mins = Right(v, 2)
> >    r.Value = TimeSerial(hrs, mins, 0)
> >    r.NumberFormat = "h:mm;@"
> > Next
> > End Sub
> > -- 
> > Gary''s Student - gsnu200774
> >
> >
> > "Greg Maxey" wrote:
> >
> >> Gary,
> >>
> >> Thanks.  This works.  I saw some odd behaviour at first but it seems to 
> >> be
> >> spot on now.  This is how my spreadsheet is laid out:
> >>
> >> I have four columns labeled:
> >>
> >> In      Out      Lunch      Total
> >>
> >>
> >> Each following cell the four columns is formatted for time.  I enter the
> >> start time like  07:05  an end time like 16:15   a standard 0:30 for 
> >> lunch
> >> and column D has a forumla:
> >> =(B2-A2-C2) which gives a total time in hours and minutes.
> >>
> >> I have six rows laid out similiar to the above (Mon - Sat).  In Cell D8 I
> >> nave teh forumual =(SUM(D2:D7)).  This gives me the time in hours and
> >> minutes.
> >>
> >> I placed the formula that you provided in Cell D9.  I formatted it as
> >> "General" and then "Custom" "0.00"
> >>
> >> The first time I tried this the odd behaviour was showing up in D8.  I
> >> entered 08:00 in the first five "In" fields and "16:30" in the first 5 
> >> "Out"
> >> fields.  I expected to see "40:00" in D8 and "40.00" in D9.  What I saw 
> >> was
> >> "16:00" in D8 and "40.00" in D9.  I tried a few changes and each time the 
> >> D9
> >> value was correct but the D8 value was low.  Finally I set all the value 
> >> to
> >> 0 and started over.  It worked perfectly.
> >>
> >> Any idea what caused the behaviour described above?  I can't imagine how 
> >> the
> >> application gets "40.00" out of "16:00 * 24"  then again I don't 
> >> understand
> >> how it gets "40.00" out of "40:00 * 24" either.
> >>
> >> A few more questions if I may.
> >>
> >> 1.  I would like to "reset" the time in and time out values to 08:00 - 
> >> 16:00
> >> after I compute the totals for each employee.  I know nothing about Excel
> >> VBA but I know enough about Word VBA to be certain that it could be done. 
> >> I
> >> suppose the code would look something like:
> >>
> >> For Each oCell in oRng.Cells
> >>   oCell.Value = "0:00"
> >> Next oCell
> >>
> >> 2.  Is there a way to autoformat the text entry?  It would be much more
> >> efficient if I could enter 0700 (without the colon) and the cell content
> >> autoformat to 07:00.
> >>
> >> Thanks again.
> >>
> >>
> >>
> >>
> >> -- 
> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >> Greg Maxey -  Word MVP
> >>
> >> My web site http://gregmaxey.mvps.org
> >> Word MVP web site http://word.mvps.org
> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >>
> >>
> >>
> >> Gary''s Student wrote:
> >> > =A1*24 and format as General.
> >> >
> >> > If your app absolutely requires two and only two decimal digits, then:
> >> >
> >> > Format > Cells... > Number > Number > and specify 2 digits
> >> >
> >> >> I have a simple spreadsheet for determining the hours and minutes
> >> >> that an employee works during a pay period.  The current result is
> >> >> formatted like "38:45"  meaning 38 hours and 45 minutes.  My payroll
> >> >> software needs the time worked in decimal format (i.e., 38.75).
> >> >>
> >> >> Can someone please show me how I would use an Excel formula to
> >> >> covert hours and minutes to hours and fraction of hours.  Thank you.
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >> >> Greg Maxey -  Word MVP
> >> >>
> >> >> My web site http://gregmaxey.mvps.org
> >> >> Word MVP web site http://word.mvps.org
> >> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >>
> >>
> >> 
> 
> 
> 
0
GarysStudent (1572)
3/19/2008 11:33:15 PM
Gary,

Thanks.

I think I have it sorted out and understand what was going on.  When I 
chagned D8 to General it's value changed to 1.666667.  I realized that 40 
hours is 1.666667 days and apparently the format I was using for that cell 
made 1.666667 days looke like 16 hours.

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey -  Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Gary''s Student wrote:
> There are several things to check:
>
> 1. compare the formats of the cells that are correct to the cells
> (like D8) that are not correct.  It might just  a formatting issue.
>
> 2. Insure that D2 thru D7 are genuine numbers.  SUM() ignores
> non-numbers and won't even tell you.
>
> If worst come to worst, select D2 thru D8 and change the format to
> General. It will then be easier to check the math!!
>
> Update this post if problem persist.
>
>> Gary,
>>
>> Thanks for the code.  I modified is slightly so that it doesn't
>> effect the row for Saturday which is normally not a workday and set
>> at 0:00.  It works perfectly.
>>
>> However, I am still seeing the odd value in Cell D8.  When I reset
>> the values D8 reads "16:00" while D9 reads "40:00"
>>
>> Again. D8 uses a formula =Sum(D2:D7) and the formula in D9 is
>> =(D8*24).
>>
>> The value in D2, D3, D4, D5, and D6 is "8:00" the value in D7 is
>> "0"00"
>>
>> How can D8 read "16:00" if is the sum of 8:00+8:00+8:00+8:00+8:00  ??
>>
>> How can D9 read "40:00" if it is the sum of 16:00*24  ??
>>
>>
>>
>> --
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Greg Maxey -  Word MVP
>>
>> My web site http://gregmaxey.mvps.org
>> Word MVP web site http://word.mvps.org
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>>
>> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in
>> message news:E825CF3C-D954-4182-B5BF-5BA1C72D0B5C@microsoft.com...
>>> With regard to resetting values to 8:00 and 16:00, try this code :
>>>
>>> Sub time_reset()
>>> n = Cells(Rows.Count, 1).End(xlUp).Row
>>> For i = 2 To n
>>>    Cells(i, 1) = #8:00:00 AM#
>>>    Cells(i, 2) = #4:00:00 PM#
>>> Next
>>> End Sub
>>>
>>> It simply fills the used portions of columns A & B with the desired
>>> reset values.
>>>
>>>
>>> Starting with an empty column that has been formatted to Text, enter
>>> values
>>> like:
>>> 0534
>>> 0811
>>> 1130
>>> without any colons and then select the cells and then run:
>>>
>>> Sub time_converter()
>>> For Each r In Selection
>>>    v = r.Value
>>>    hrs = Left(v, 2)
>>>    mins = Right(v, 2)
>>>    r.Value = TimeSerial(hrs, mins, 0)
>>>    r.NumberFormat = "h:mm;@"
>>> Next
>>> End Sub
>>> --
>>> Gary''s Student - gsnu200774
>>>
>>>
>>> "Greg Maxey" wrote:
>>>
>>>> Gary,
>>>>
>>>> Thanks.  This works.  I saw some odd behaviour at first but it
>>>> seems to be
>>>> spot on now.  This is how my spreadsheet is laid out:
>>>>
>>>> I have four columns labeled:
>>>>
>>>> In      Out      Lunch      Total
>>>>
>>>>
>>>> Each following cell the four columns is formatted for time.  I
>>>> enter the start time like  07:05  an end time like 16:15   a
>>>> standard 0:30 for lunch
>>>> and column D has a forumla:
>>>> =(B2-A2-C2) which gives a total time in hours and minutes.
>>>>
>>>> I have six rows laid out similiar to the above (Mon - Sat).  In
>>>> Cell D8 I nave teh forumual =(SUM(D2:D7)).  This gives me the time
>>>> in hours and minutes.
>>>>
>>>> I placed the formula that you provided in Cell D9.  I formatted it
>>>> as "General" and then "Custom" "0.00"
>>>>
>>>> The first time I tried this the odd behaviour was showing up in
>>>> D8.  I entered 08:00 in the first five "In" fields and "16:30" in
>>>> the first 5 "Out"
>>>> fields.  I expected to see "40:00" in D8 and "40.00" in D9.  What
>>>> I saw was
>>>> "16:00" in D8 and "40.00" in D9.  I tried a few changes and each
>>>> time the D9
>>>> value was correct but the D8 value was low.  Finally I set all the
>>>> value to
>>>> 0 and started over.  It worked perfectly.
>>>>
>>>> Any idea what caused the behaviour described above?  I can't
>>>> imagine how the
>>>> application gets "40.00" out of "16:00 * 24"  then again I don't
>>>> understand
>>>> how it gets "40.00" out of "40:00 * 24" either.
>>>>
>>>> A few more questions if I may.
>>>>
>>>> 1.  I would like to "reset" the time in and time out values to
>>>> 08:00 - 16:00
>>>> after I compute the totals for each employee.  I know nothing
>>>> about Excel VBA but I know enough about Word VBA to be certain
>>>> that it could be done. I
>>>> suppose the code would look something like:
>>>>
>>>> For Each oCell in oRng.Cells
>>>>   oCell.Value = "0:00"
>>>> Next oCell
>>>>
>>>> 2.  Is there a way to autoformat the text entry?  It would be much
>>>> more efficient if I could enter 0700 (without the colon) and the
>>>> cell content autoformat to 07:00.
>>>>
>>>> Thanks again.
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>>> Greg Maxey -  Word MVP
>>>>
>>>> My web site http://gregmaxey.mvps.org
>>>> Word MVP web site http://word.mvps.org
>>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>>>
>>>>
>>>>
>>>> Gary''s Student wrote:
>>>>> =A1*24 and format as General.
>>>>>
>>>>> If your app absolutely requires two and only two decimal digits,
>>>>> then:
>>>>>
>>>>> Format > Cells... > Number > Number > and specify 2 digits
>>>>>
>>>>>> I have a simple spreadsheet for determining the hours and minutes
>>>>>> that an employee works during a pay period.  The current result
>>>>>> is formatted like "38:45"  meaning 38 hours and 45 minutes.  My
>>>>>> payroll software needs the time worked in decimal format (i.e.,
>>>>>> 38.75).
>>>>>>
>>>>>> Can someone please show me how I would use an Excel formula to
>>>>>> covert hours and minutes to hours and fraction of hours.  Thank
>>>>>> you.
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>>>>> Greg Maxey -  Word MVP
>>>>>>
>>>>>> My web site http://gregmaxey.mvps.org
>>>>>> Word MVP web site http://word.mvps.org
>>>>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 


0
gmaxey (30)
3/19/2008 11:56:46 PM
Reply:

Similar Artilces:

vacation available not calculating
I have an employee setup with set hours of vacation accrual per pay period. This accrual is setup using payroll. The accrual calculates fine. The employee is setup as a hourly employee, the problem is when I pay the employee vacation time the vacation available does not subtract the hours. The vacation time is setup correctly as vacation type based on hourly. I know it recognizes it as a vacation type, because if try to pay more vacation hours then available I do get a message that available hours are not being sufficient. I am not sure why the available balance is not reduced by the amo...

Auto calculating not working
For the first time in 20 years of using Excel, I am finding that auto calculation is not taking place whenever I add new entries to a range of cells included in, for example, the SUM function. I only get the correct results after invoking the function again. Even to calculate a fraction, the correct answer is not automatically updated if the numerator or denominator changes. Yesterday, I emailed the worksheet I was working on to my office neighbour's computer and I was able to edit the sheet and get the updated results automatically. Could there be something wrong with the...

Calculated field does not appear in subtotal
Ive added a calculated field to a pivot table to get a very basic percentage, one field divided by another. This works fine, however when i try adding a subtotal for one of the columns the calculated filed is missing. All of the other subtotals appear. (Indicated by ??? in below extract, this may not show up properly depending on tabs). Anybody come accross this before and can anyone help please? Dec Grand Total Plano Low High Plano SumLow Sum High Sum 712 1,405 69 11,112 22,617 1,057 34,786 20 19 10 157 286 159 602 2.81% 1.35% 14.49% ??? ??? ??? 1.73% 16 17 8 119 230 123 472 2.25%...

Time zone
When I noticed the clock on my pc was wrong, I changed the time zone from Pacific to Eastern which is my correct U.S. time zone. After doing this, all of the items inserted as "all day" appointments in my monthly calendar are displayed with an image of a clock on either side of the appointment. The option to display clocks is turned off in the settings. Why is this happening and how can I prevent it from happening. Right now, I've had to put back the Pacific time zone and adjust to the pc not telling the right time. Is there any other solution? Thanks! ddlaz No ...

Send/Receive continues 1000s of times
When Outlook does its regular send/receive it ends up sending and receiving thousands of times. The task pane shows an ever increasing number of tasks that it says have been completed successfully. When all it actually is doing is rechecking the mail accounts I have, over and over again. Literally thousands of times. This interferes with anything that I want to do in Outlook while this is going on. It seems to have started after I downloaded either the latest version of itunes or the latest version of Adobe (or some other application like that). Help! And thanks. Per Roady - MVP, th...

One time code
In either an SDI or MDI application, if I want to include some functionality ( such as serial port configuration and control thread) that can only be run once per application instance, where should that code go? I looked at the App class but it doesn't handle user messages ( at least by default ). Should the main code and worker thread go in the MainFrm class in the OnCreate function? Help? Ron H. ----------------- www.Newsgroup-Binaries.com - *Completion*Retention*Speed* Access your favorite newsgroups from home or on the road ----------------- If it has to do with the "wi...

Reducing repetitive calculation not working
A B C 1 Fruit Qty =INDEX($1:$1048576,2,2):INDEX($1:$1048576,4,2) 2 Apple 1 3 Orange 2 4 Apple 3 Why does =SUMIF(A2:A4,"Apple",B2:B4) return the correct value ... .... while =SUMIF(A2:A4,"Apple",C1) returns a #VALUE error ? The reason I'm not including the formula in C1 as the sum_range in the SUMIF formula is to reduce the number of duplicate calculations (100,000+). I tried creating a name with the formula in C1 and using the name in the SUMIF formula as the sum_range and that works. But since the sum_range changes, I would have to use the OFFSET function to inc...

cpu time
Hi, Can you tell me about good tool for vs-6 that can tell me what functions in my application take the most cpu time? Thanks! In article <u$YZa3o9EHA.2680@TK2MSFTNGP09.phx.gbl>, dbg@012.net.il says... > Hi, > Can you tell me about good tool for vs-6 that can tell me what functions in > my application take the most cpu time? VS 6 includes a perfectly good profiler, which does exactly this. If you're working inside the environment, go to the link tab in the project settings, and click the "enable profiling" check box, then do a 'rebuild all' to gene...

Time Out a MsgBox
I have a file that runs a macro when it is open. This file may be opened manually or automatically by a macro from another file. If the file is opened manually I would like a MsgBox to prompt the user to end the macro before it gets into the body of the code. If the file is opened automatically I would like the MsgBox to automatically respond after a time delay to allow the macro to continue running the bulk of the code. Is there anyway to force a response to a MsgBox after a preset amount of time with no response from the user? Hi, How about this instead which closes...

Calculating time and pay in excel
Hi, i'm trying to make a spreadsheet that can calculate pay per minute depending on an average length of time worked. Its driving me crazy and any help would be greatly appreciated! Coloumn A is length of time, at the bottom is a total time, next to that is an average length of time worked and this is then multiplied by the amount appropriate for that average. What would be the best way to work all of this out accurately? Many thanks for any help you can give Mark -- wobbleman ------------------------------------------------------------------------ wobbleman's Profile: http://www.e...

Start Time & End Time
I have a table of data for my Scheduled cutting list that is exported from another program into an Excel Spreadsheet and from this table I would like to be able to find the start and end time of the production runs automatically in a separate table. The table is set up like this Planned order Time Product Description Volume 12345 08:00:00 Product 1 3500 23435 11:32:00 Product 2 5680 14567 13:24:00 Product 3 10000 and so on. I can find the start time by us...

Am I behind the times?
I have just installed Exchange sp2 on my SBS 2008 boxes and all went well. One weird thing at the end of one of the installs it said Web Apps (I think) couldn't restore default configuration, but everything seems to be working just fine. So then I went to look for updates and I see a few that I must have missed. I use WSUS on the network. One is Exchange SP2 update rollup 3. Go for it or no? Second, Windows Server 2008 SP2. Yikes, when did that come out for SBS? I wonder why it never popped up before since it looks like it's been out for quite some time. Nonetheless, s...

run time error
When I attempt to import a text file (.txt) in to Access, an error occurs. Run-time error �3163�: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data When I click the Debug button this pops up: .Fields("TranType").Value = rs("MoveType").Value A different error occurs when save it to my desktop, and this is what happens.Run-time error �2185�: You can�t reference a property or method for a control unless the control has the focus. When I click the Debug button this pops up: MsgBox "Import File Not Found:&quo...

Transferring freight more than one time on a sales order
We frequently have partial shipments of sales orders and we leave the sales order open until everything has shipped instead of creating backorders. The system will allow you to have several tracking numbers for each order but you can only transfer the freight once. I would like to make the suggestion to be able to transfer freight more than one time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, ...

Incorrect Time Stamps
Exchange seems to be stamping the wrong time on emails and events sporadically. All the times on servers are correct. Any thoughts? -- Craig Tuttle - Level I Exchange Tech UCNSB.org The time stamp can also be handled by the client. Which exact time field is being stamped wrong? Have you verified that the client has the correct time (i.e. Daylight savings time set correctly)? -- Ben Winzenz Exchange MVP MessageOne "Craig Tuttle" <CraigTuttle@discussions.microsoft.com> wrote in message news:02D51926-77DC-43EB-A5B5-7A00F4530323@microsoft.com... > Exchange seems to b...

how do I stop re-calculated fields in excel from going "black"
When I change any cells in my excel spreadsheet and the sheet re-calculates any affected cells go black i.e. I can't see the new calculated figures in these cells? If I hit "F2 and then enter" the cell goes back to normal i.e I can see things normally? Hi there Pat, I assume you are using office 2003, You might want to set your calculations to manual. Tools>Options>Calculations : Then set to manual Hope this works for you Regards Garreth -- Thank you and Regards Garreth Lombard "Pat" wrote: > When I change any cells in...

How chart time scale x-axis with another date serie
Hello all. I have this problem, I will need to create this chart that I don't know how to. Serie 1 is below which needs to be time scaled Date Count 1/1/2006 12 1/2/2006 3 1/3/2006 5 2/1/2006 18 2/3/2006 4 2/18/2006 9 4/1/2006 18 7/4/2006 38 Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis with symbols. 1/31/2006 2/18/2006 4/2/2006 I will look something like you have a line of count on a time-scaled chart, with 3 dates point in the x-axis. Please ...

Activity default due time
I need to stop the default coming up as 12:00am and set it to something like 9am so people get reminders at reasonable times. How can this be done? thanks Adrian Adrian, If you don't mind writing a few lines of code, look at "Setting a default time in a date field" in Client Side Scripting - More JavaScript Code - Part 3 (http://www.stunnware.com/crm2/topic.aspx?id=JS12). -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup The ...

Time / Negative Time
Hi, If, for instance, in Excel you write 17:00 - 16:30, it will produc 0:30. However, 16:30 - 17:00 will produce #####, understandibly because you cannot have - ? o'clock. By formatting this to a number can produce -0.5. What if I still want to represent the decimal, but out of 60, so that can still get -0.30. I have tried messing about with fractions and still can't get it. Any ideas? Thanks, -- Message posted from http://www.ExcelForum.com On Thu, 13 May 2004 17:33:58 -0500, Kiaat <<Kiaat.167uek@excelforum-nospam.com>> wrote: >Hi, > >If, for insta...

How to Calulculate Hrs and Mins between time frames
This is a multi-part message in MIME format. ------=_NextPart_000_0293_01C68406.3D280160 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable If i have 2 cells with time values say: A1=3D7:30AM and B1=3D3:30PM. 7:30AM - 3:30PM set to NORMAL Hours 3:30PM - 6:30PM set to TIME & HALF Hours Any time after 6:30PM - 7:29AM to be DOUBLE TIME hours Then if i set 4 other cells for data input: A2=3D( DATE 1) 29/5/2009 Start date. B2=3D(Date 2) 29/5/2006 = Finish date. A3=3D(Time) 7:30AM Start Time. B2=3D (Time) 7:00PM =...

Time segments in OWA
I need to change the time segments in OWA when creating new appointments. The default is 30 minutes but I need 20 minutes. I can't seem to find the right code in the exchsrvr exchweb controls. I'm close but can't find the time array expression tha generates the 30 minute increments. Can anybody help me with this? Thanks, Brian Techhead <jorgenson.b@gmail.com> wrote: > I need to change the time segments in OWA when creating new > appointments. The default is 30 minutes but I need 20 minutes. I can't > seem to find the right code in the exchsrvr exchweb c...

calculate time and date
I want to find out how many houer iy is between one date/time and another date/time. date and time one cell A1= 04.03.2005 08:30 date/time two cell A2= 05.03.2005 23:59 =A2-A1 custom format [hh:mm] or for Norway [tt:mm] -- Regards, Peo Sjoblom "make" <lage@tiscali.no> wrote in message news:2c61523.0503070721.2dacdd07@posting.google.com... > I want to find out how many houer iy is between one date/time and > another date/time. date and time one cell A1= 04.03.2005 08:30 > date/time two cell A2= 05.03.2005 23...

How Can I Know last time was activity on a database
I'm doing a process that uses databases from sys.databases. I would like to know when is the last time happened some activity there. (insert, update) I've tryed to look at sys.tables but only shows me last structural change on it. Not last time data has changed. How this can be done? Carles Oriol SQL Server doesn't store this information (imagine the overhead for all those who doesn't need it). You can for instance use a trigger to keep track of that information. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sq...

Search for documents by time modified
I need to narrow a search for documents by author, a range of time (say from 5 p.m. to 8 p.m.), and a range of years (say from 1998 to 2004). They would need to include Word Perfect and Word documents. Thanks for any help. Version of Windows? Use Advanced Search. But if you're using Word2007 in Windows XP, you might not have much of a search function (it pretty much got moved from Office to Windows with the new versions). On Feb 19, 9:01=A0pm, D. Ring <D. Ring @discussions.microsoft.com> wrote: > I need to narrow a search for documents by author, a range ...

Time, time, time, always an issue
Here’s my problem: I have a date/time field recording the date/time when pt arrives at our hospital. My final output needs to include 2 new fields: - day of week pt arrived: Sun, Mon, Tues, etc (this I can do) - time pt arrived, renamed as: Regular (6am – 5:59 pm) and Evening (6pm - 5: 59 am) This one I have no idea!! Any Suggestions? Thanks. Phredd wrote: >I have a date/time field recording the date/time when pt arrives at our >hospital. >My final output needs to include 2 new fields: >- day of week pt arrived: Sun, Mon, Tues, etc (this I can do) >- time pt arrived, re...