Time Calculation

A

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.
 
T

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
 
A

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"
 
A

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
 
J

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
 
A

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

Jacob Skaria said:
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


ap1971 said:
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"
 
J

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


ap1971 said:
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

Jacob Skaria said:
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


ap1971 said:
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.
 
A

ap1971

Thanks! Missed it.

Jacob Skaria said:
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


ap1971 said:
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

Jacob Skaria said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top