Time Calculation

[ap1971]

Ok. Calculating a time sheet. Entries are as follows

Columns DEFG Have Time In, Time Out, Time In, Time Out headings. I
calculated total hours by: =(E5-D5)+(G5-F5) Have cell formatted as h:mm so
the time displays actual hours and minutes worked ex: 8:13 (8 Hours/13
Minutes). Now to that number I want to add additional time such as Sick
Leave 1 (hour), 1.5, .25. How can I calculate. If necessary I probably can
divide the additional time entrys into two cells with hours in one and
minutes in another. If you can help with calculation and cell formatting,
I would appreciate it.

 

[Jacob Skaria]

g24 = 1.5
=(E25+F25)-(C25+D25)+G24/24

If this post helps click Yes

 

[T. Valko]

I want to add additional time such as

Sick Leave 1 (hour), 1.5, .25.

If you use an additional cell and enter the amount to add as a decimal
number:

=(your_formula)+cell_ref/24

A1 = 0.25 (15 minutes)

=(your_formula)+A1/24

 

[ap1971]

I don't think so. (Hopefully the copy/paste below will come across OK)

4 D E F G H I J K
5 Start End Start End Total H M Total
6 7:52 AM 12:30 PM 1:25 PM 5:00 PM 8:13 1 30 9:43
7 7:00 AM 12:00 PM 12:30 PM 4:00 PM 8.00 30 8:30

Formula to calculate "H" is =(E6-D6)+(G6-F6)
"H" Cells formatted as h:mm

My desired answer is "K"

 

[ap1971]

Thought it would be easier to display. Hopefully copy/paste works. See below

4 D E F G H I J K
5 Start End Start End Total H M Total
6 7:52 AM 12:30 PM 1:25 PM 5:00 PM 8:13 1 30 9:43
7 7:00 AM 12:00 PM 12:30 PM 4:00 PM 8.00 30 8:30


Formula for H =(E6-D6)+(G6-F6)
Formatted as h:mm

K is my desired result

 

[Jacob Skaria]

Try the formula in K6

=H6+(I6/24)+(J6/1440)

and format the total cells to custom format [h]:mm so that anything more
than 24 are displayed as so.

If this post helps click Yes

 

[ap1971]

Thanks that worked. Not trying to sum all of my totals using simple
=SUM(K6:K10)
and it is not correct. What do you suggest as the formula?


My

Try the formula in K6

=H6+(I6/24)+(J6/1440)

and format the total cells to custom format [h]:mm so that anything more
than 24 are displayed as so.

If this post helps click Yes
---------------
Jacob Skaria

I don't think so. (Hopefully the copy/paste below will come across OK)

4 D E F G H I J K
5 Start End Start End Total H M Total
6 7:52 AM 12:30 PM 1:25 PM 5:00 PM 8:13 1 30 9:43
7 7:00 AM 12:00 PM 12:30 PM 4:00 PM 8.00 30 8:30

Formula to calculate "H" is =(E6-D6)+(G6-F6)
"H" Cells formatted as h:mm

My desired answer is "K"

 

[Jacob Skaria]

Read my earlier post in full ..You have missed the below..Right click the
cell>Format Cells>Custom>Type:
[h]:mm

If this post helps click Yes
---------------
Jacob Skaria

Thanks that worked. Not trying to sum all of my totals using simple
=SUM(K6:K10)
and it is not correct. What do you suggest as the formula?


My

Try the formula in K6

=H6+(I6/24)+(J6/1440)

and format the total cells to custom format [h]:mm so that anything more
than 24 are displayed as so.

If this post helps click Yes
---------------
Jacob Skaria

I don't think so. (Hopefully the copy/paste below will come across OK)

4 D E F G H I J K
5 Start End Start End Total H M Total
6 7:52 AM 12:30 PM 1:25 PM 5:00 PM 8:13 1 30 9:43
7 7:00 AM 12:00 PM 12:30 PM 4:00 PM 8.00 30 8:30

Formula to calculate "H" is =(E6-D6)+(G6-F6)
"H" Cells formatted as h:mm

My desired answer is "K"


:

g24 = 1.5
=(E25+F25)-(C25+D25)+G24/24

If this post helps click Yes
---------------
Jacob Skaria


:

Ok. Calculating a time sheet. Entries are as follows

Columns DEFG Have Time In, Time Out, Time In, Time Out headings. I
calculated total hours by: =(E5-D5)+(G5-F5) Have cell formatted as h:mm so
the time displays actual hours and minutes worked ex: 8:13 (8 Hours/13
Minutes). Now to that number I want to add additional time such as Sick
Leave 1 (hour), 1.5, .25. How can I calculate. If necessary I probably can
divide the additional time entrys into two cells with hours in one and
minutes in another. If you can help with calculation and cell formatting,
I would appreciate it.

 

[ap1971]

Thanks! Missed it.

Read my earlier post in full ..You have missed the below..Right click the
cell>Format Cells>Custom>Type:
[h]:mm

If this post helps click Yes
---------------
Jacob Skaria

Thanks that worked. Not trying to sum all of my totals using simple
=SUM(K6:K10)
and it is not correct. What do you suggest as the formula?


My

Try the formula in K6

=H6+(I6/24)+(J6/1440)

and format the total cells to custom format [h]:mm so that anything more
than 24 are displayed as so.

If this post helps click Yes
---------------
Jacob Skaria


:

I don't think so. (Hopefully the copy/paste below will come across OK)

4 D E F G H I J K
5 Start End Start End Total H M Total
6 7:52 AM 12:30 PM 1:25 PM 5:00 PM 8:13 1 30 9:43
7 7:00 AM 12:00 PM 12:30 PM 4:00 PM 8.00 30 8:30

Formula to calculate "H" is =(E6-D6)+(G6-F6)
"H" Cells formatted as h:mm

My desired answer is "K"


:

g24 = 1.5
=(E25+F25)-(C25+D25)+G24/24

If this post helps click Yes
---------------
Jacob Skaria


:

Ok. Calculating a time sheet. Entries are as follows

Columns DEFG Have Time In, Time Out, Time In, Time Out headings. I
calculated total hours by: =(E5-D5)+(G5-F5) Have cell formatted as h:mm so
the time displays actual hours and minutes worked ex: 8:13 (8 Hours/13
Minutes). Now to that number I want to add additional time such as Sick
Leave 1 (hour), 1.5, .25. How can I calculate. If necessary I probably can
divide the additional time entrys into two cells with hours in one and
minutes in another. If you can help with calculation and cell formatting,
I would appreciate it.

 

[Ashish Mathur]

Hi,

Say your formula is in cell F8. In cell F10, you type 1.5. In cell F12,
you may enter =TIME(,F10*60,)+F8

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com