Time Card Issue

Emoncada

Active Member
Joined
Mar 23, 2005
Messages
409
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If your data is in the range B1:B4:

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

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

Emoncada

Active Member
Joined
Mar 23, 2005
Messages
409
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?
 

Emoncada

Active Member
Joined
Mar 23, 2005
Messages
409

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

Active Member
Joined
Mar 23, 2005
Messages
409

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

Active Member
Joined
Mar 23, 2005
Messages
409
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

Active Member
Joined
Mar 23, 2005
Messages
409
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.
 

Forum statistics

Threads
1,136,430
Messages
5,675,794
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top