time sheet am / pm

[Jeff]

Hi,

Wondering if someone can help. I am just trying to make a simple like time
sheet.

Time In Time Out Toatal Hours Worked
8.05 4.30 8.25

i would like this for each day of the week then at the end have a toal hours
for the week worked.

I can not figure out the formating to put in and then be able to get the
right ending total. If i formate the cell as time (h:mm) the i have to type
in 8:05 and then that does not seem to work for pm because when I type 4:30
it thinks it is am, so I guess that you need to know military time?

Is there a simple way to just be able to type 8.05 4.30 and it knows the
first column is AM and the second column is PM and then the toal column is
also in time? When I tried putting it in all as number or text when I got
to the grand total it did not know it was time so went off based on 100 and
not 60 minutes.

I guess I am confused how to make it simple to type in but have it calculate
correctly?

Thanks for the Help.

Jeff

 

[Gord Dibben]

Jeff

Chip Pearson has a site on Timesheets which explains most of this quite well.

http://www.cpearson.com/excel/overtime.htm

See also at the bottom of page the links to other pages working with dates and
times.


Gord Dibben MS Excel MVP

 

[Harlan Grove]

I can not figure out the formating to put in and then be able to get the
right ending total. If i formate the cell as time (h:mm) the i have to type
in 8:05 and then that does not seem to work for pm because when I type 4:30
it thinks it is am, so I guess that you need to know military time?

No, you need to enter hour, colon, minute, then AM or PM.

If you only have day shifts, so start time is always between midnight
and noon and end time is always between noon and midnight, then add
0.5 to the second column (end time) values in other formulas. So if
start time were in col B, end time in col C and total time in col D,
the total time for the record in row 3 would be given by the formula

=C3+0.5-B3

However, if there could be ANY exceptions to this, e.g., ending time
also before noon, beginning time after noon, swing shifts or graveyard
shifts, then you MUST enter AM/PM as appropriate. Computers can't deal
with ambiguity, so humans have to be explicit and complete sometimes.

Is there a simple way to just be able to type 8.05 4.30 and it knows the
first column is AM and the second column is PM and then the toal column is
also in time? When I tried putting it in all as number or text when I got
to the grand total it did not know it was time so went off based on 100 and
not 60 minutes.

As is perfectly reasonable for Excel. If you want to use h.mm
notation, it's entirely up to YOU to handle transitions properly.
Using the same sample cell addresses as above, total time (time
elapsed between start time and end time) would be given by the formula

=TIME(INT(C4),100*MOD(C4,1),0)+0.5-TIME(INT(B4),100*MOD(B4,1),0)

HOWEVER, as noted above, if you EVER have to handle start and end
times on the SAME side of noon, then YOU AND YOUR USERS would need to
enter AM or PM or times using 24 hour clock notation (military time).

Further complicating matters would be swing or graveyard shifts on
either side of midnight. As long as no one would ever work more than
23 hours 59 minutes at a time AND you and your users enter either AM/
PM with 12 hour clock times or enter 24 hour clock military times, you
could calculate time worked as

=MOD(C3-B3,1)

 

[bj]

just type in the PM in a formated as "hh:mm AM/PM" cell
"4:30 PM" will be a time

 

[BoniM]

Enter all values as hour:minutes (it's okay if they will all appear as AM in
formula bar)
Total daily hours with:
=MOD(B3-A3,0.5)
Time In Time Out Total Hours
8:05 4:30 8:25
Format all cells as custom: h:mm
Total weekly hours with the sum function and format that cell as [h]:mm.
(In a time format, h is for hour, a number between zero and 23. [h] is for
elaspsed time and allows you to go above 23.)

This formula will only be accurate as long as hours worked are <12.
Good luck!

 

[Jeff]

Thanks to all for the reply's and help!

How about this then to help keep the complication down for someone. Since i
am going to have a colum marked as am and pm is it somehow possible to
automatically conocate the "AM" and "PM" on to the entered times either in
the same column that they enter these values or possibly in a hidden column
that I could base the formula off of?

Just wondering if this could be done so that the person entering the time
just hast to put in 8:05 4:30 and not have to actually worry about typing
in the am or pm.? I know this is all assuming that the people will start in
the "AM and end in the "PM" i will work out that problem once i can solve
this question.

Thanks again.

Jeff

 

[Harlan Grove]

How about this then to help keep the complication down for someone. Since i
am going to have a colum marked as am and pm is it somehow possible to
automatically conocate the "AM" and "PM" on to the entered times either in
the same column that they enter these values or possibly in a hidden column
that I could base the formula off of?

....

If you have 12 hour clock hour/minute entries in columns B and E, and
AM/PM entries in columns C and F corresponding to entries in B and E,
respectively, and you wanted to calculate time worked in column H, try
this for row 3.

H3:
=(TEXT(E3,"h:mm ")&F3)-(TEXT(B3,"h:mm ")&C3)

 

[Jeff]

Thanks this worked out well. Ok so here would be my next question then.

I have the end of week total and that cell is formatted as suggested below
[h]:mm, the new problem I am having is that I have a cell for hourly rate
but when I multiply Total Hours Worked by Hourly Rate I am getting a wrong
amount. Example:

Total Hrs 20:30 x $10 Hourly Rate is giving me $8.54

How can I now get the Hourly rate to recognize Total Hours as a number?

Thanks again for the help.

Jeff

Enter all values as hour:minutes (it's okay if they will all appear as AM
in
formula bar)
Total daily hours with:
=MOD(B3-A3,0.5)
Time In Time Out Total Hours
8:05 4:30 8:25
Format all cells as custom: h:mm
Total weekly hours with the sum function and format that cell as [h]:mm.
(In a time format, h is for hour, a number between zero and 23. [h] is
for
elaspsed time and allows you to go above 23.)

This formula will only be accurate as long as hours worked are <12.
Good luck!

Hi,

Wondering if someone can help. I am just trying to make a simple like
time
sheet.

Time In Time Out Toatal Hours Worked
8.05 4.30 8.25

i would like this for each day of the week then at the end have a toal
hours
for the week worked.

I can not figure out the formating to put in and then be able to get the
right ending total. If i formate the cell as time (h:mm) the i have to
type
in 8:05 and then that does not seem to work for pm because when I type
4:30
it thinks it is am, so I guess that you need to know military time?

Is there a simple way to just be able to type 8.05 4.30 and it knows
the
first column is AM and the second column is PM and then the toal column
is
also in time? When I tried putting it in all as number or text when I
got
to the grand total it did not know it was time so went off based on 100
and
not 60 minutes.

I guess I am confused how to make it simple to type in but have it
calculate
correctly?

Thanks for the Help.

Jeff

 

[Harlan Grove]

I have the end of week total and that cell is formatted as suggested below
[h]:mm, the new problem I am having is that I have a cell for hourly rate
but when I multiply Total Hours Worked by Hourly Rate I am getting a wrong
amount. Example:

Total Hrs 20:30 x $10 Hourly Rate is giving me $8.54

How can I now get the Hourly rate to recognize Total Hours as a number?

....

You need a tutorial on how times (and dates) work in Excel.

Times are fractions of days, so 1:30 AM equals 1/24 + 1/48 = 3/48 =
1/16 = 0.0625.

Also, number formatting has NO EFFECT WHATSOEVER on values. A time
value formatted to appear as time, [h]:mm, of 20:30 is EXACTLY THE
SAME as the number 41/48 = 0.854166667. It's NOT equal the number
20.5.

Convert times (fractions of days) to equivalent number of hours by
multiplying them by 24. The formula

="20:30"*24*10

returns 205.

 

[Jeff]

Thanks Harlan,

this is what I figured out, I took the 20:30 times 24 then times the hourly
rate and that made it come out correctly.

Thanks for the help.

Jeff

I have the end of week total and that cell is formatted as suggested below
[h]:mm, the new problem I am having is that I have a cell for hourly rate
but when I multiply Total Hours Worked by Hourly Rate I am getting a wrong
amount. Example:

Total Hrs 20:30 x $10 Hourly Rate is giving me $8.54

How can I now get the Hourly rate to recognize Total Hours as a number?

...

You need a tutorial on how times (and dates) work in Excel.

Times are fractions of days, so 1:30 AM equals 1/24 + 1/48 = 3/48 =
1/16 = 0.0625.

Also, number formatting has NO EFFECT WHATSOEVER on values. A time
value formatted to appear as time, [h]:mm, of 20:30 is EXACTLY THE
SAME as the number 41/48 = 0.854166667. It's NOT equal the number
20.5.

Convert times (fractions of days) to equivalent number of hours by
multiplying them by 24. The formula

="20:30"*24*10

returns 205.