Time Card Issue

[Emoncada]

I have a time card on a spreadsheet I would like so the time at the end of the day will tell me how many hours worked without AM Or PM just 7.30 hrs. 8.00 hrs. 7.59 hrs. etc...

example I have

In.........8:00 AM
Out.......12:00 PM
In..........1:00 PM
Out........4:30 PM

Total......7.30 hrs

Any Ideas.

 

[Andrew Poulsom]

If your data is in the range B1:B4:

=SUM(B2,B4)-SUM(B1,B3)

formatted as [h]:mm will return 7:30.

 

[Emoncada]

Looks good Can I ask is there a formula so that when a time is entered in the first IN it will know it's AM and then if when a time is entered above 12:00 it will know it's PM?

 

[Brian from Maui]

See,

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

 

[Emoncada]

That has a lot of information, but doesn't see me have what I am looking for. I would like to keep it in normal time not military time. If i can have a formula like if time is greater than 11:59 than PM, but have that include 1:00 threw 12:00 PM. I don't think this will be easy, or possible.

 

[Brian from Maui]

That has a lot of information, but doesn't see me have what I am looking for. I would like to keep it in normal time not military time. If i can have a formula like if time is greater than 11:59 than PM, but have that include 1:00 threw 12:00 PM. I don't think this will be easy, or possible.

Excel treats time as fractions of a day (1). Thus one hour is 1/24 or 0.0416666666666667. You can format the cell to display however you want, but the underlying value will be fractions of a day. What is "normal" to you maybe completely different as to how Excel treats time.

 

[Emoncada]

I know what you mean. How would you approach this and what do you recommend. I was reviewing the vb codes on that site. They have one with case's. Maybe something like that possible changing stuff around or adding some stuff can accomplish it.

 

[Brian from Maui]

I know what you mean. How would you approach this and what do you recommend. I was reviewing the vb codes on that site. They have one with case's. Maybe something like that possible changing stuff around or adding some stuff can accomplish it.

Play around with formatting the cells, with the AM/PM option before using VBA. How would you enter times or have Excel recognize it as being AM or PM? Would you enter 11:59 and have it recognize it as AM, then how would you enter 11:59 and recognize it as PM?

 

[Emoncada]

To be honest I don't know now how I can make this work. Well I guess If I leave it as is with the case vb, then even if they put 100 = 1:00 whether it's AM or PM It will just say 1:00 and if they leave at 5:00 AM it will subtract right. The match would still be right.

 

[Emoncada]

The problem with that is that it can't have a negative time. So that won't work. So if someone comes in at 8:00 and the person takes lunch(OUT) at 1:00 that would be a -7. That won't work.