Time Card Help - easy Formulas?

[Grizzle33]

Hello everyone,

Thanks in advance to anyone helping with this.
I am having a issue creating a SIMPLE time card.


I just need help with the formulas. I have been manually entering and figuring out exact time for each employee, and I figured this could help me.
I've searched everywhere for a decent time card I could utilize, haven't found one yet.
Any help is greatly appreciated.
here's the image below, sorry for the size.

 

[Grizzle33]

I wanted to edit this a bit, but I cannot find that option.

I only really need help with one day, and I can figure out the rest myself. Thanks!

-Josh

 

[CDICKENS]

Total hours in f4 formula should be c4-c4)+(E4-D4)

make sure the column is formatted as hours.

 

[lrobbo314]

I have this for F4

=IF(((C4-B4)+(E4-D4))*24=0,"",((C4-B4)+(E4-D4))*24)

 

[Jeff Meyers]

The formulas / calculations can be straightforward and simple - as long as your time inputs utilize exact values (i.e. using the correct dates).

Excel time values are a component of a date value. See below for examples: Cells A1 through A6 all contain the exact same value - they are just displayed with different formats. If you were to input just a time - we'll use 8:00 AM - Excel records that as 1/0/1900 8:00:00 AM - even if you do not see the date.


I believe that the key factor for you would be to use exact / valid dates and times - even if you only display the time (by formatting the cells). Then your calculation becomes a very simple mathematical formula. Note that the additions and subtractions in this formula (Cell F9) are calculating the portion / fraction of a day (i.e. 24 hours) so this value is multiplied by 24 to convert it to hours.

Sheet1

	A	B	C	D	E	F
1	07/24/12 8:00 AM
2	07/24/12
3	Tuesday, July 24, 2012
4	8:00 AM
5	8:00
6	41,114.33
7						TOTAL
8		IN	OUT	IN	OUT	HOURS
9		8:00 AM	11:30 AM	12:00 PM	4:30 PM	8.00
10
11	------------------------------	--------------	--------------	--------------	--------------	--------------

<tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula F9 =(E9-D9+C9-B9)*24 <tbody> </tbody>

<tbody>
</tbody>

 

[CSyx]

Sorry text keeps getting cut off.

 

[Wulf]

I've got one for you. Give me an email addy to send it to!

 

[Grizzle33]

One more issue I'm having and then it should be golden. If someone is off on Vacation how can I make the time show up in that column? I could just leave the day blank, and manually enter their vacation hours in G14 - And then it would total correctly in the Weekly Totals. Any ideas to have it be done automatically though?

 

[jdsouza]

If you want to format the IN & OUT cells simply as Text and enter data in 4 figures like this ..

G	H	I	J	K
IN	OUT	IN	OUT
0000	1500	1600	1700	'=((LEFT(H16, etc

<tbody>
</tbody>

Assuming that the above times are in row 16, Enter the formula below in the Cell K16 to manipulate/convert the textual entries to their numeric value related to a calendar day. As written by previous poster, a calendar day = 1. One Hour = 1/24. One Minute = 1 / (24*60).

=((LEFT(H16,2)*60+RIGHT(H16,2))-(LEFT(G16,2)*60+RIGHT(G16,2))+LEFT(J16,2)*60+RIGHT(J16,2)-LEFT(I16,2)*60+RIGHT(I16,2))/60

G	H	I	J	K
IN	OUT	IN	OUT
0000	1500	1600	1700	16.5

<tbody>
</tbody>