# Time Card Help - easy Formulas?

This is a discussion on Time Card Help - easy Formulas? within the Excel Questions forums, part of the Question Forums category; Hello everyone, Thanks in advance to anyone helping with this. I am having a issue creating a SIMPLE time card. ...

1. ## Time Card Help - easy Formulas?

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.

2. ## Re: Time Card Help - easy Formulas?

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

3. ## Re: Time Card Help - easy Formulas?

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

make sure the column is formatted as hours.

4. ## Re: Time Card Help - easy Formulas?

I have this for F4

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

5. ## Re: Time Card Help - easy Formulas?

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 ------------------------------ -------------- -------------- -------------- -------------- --------------
Excel 2010

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

6. ## Re: Time Card Help - easy Formulas?

Sorry text keeps getting cut off.

7. ## Re: Time Card Help - easy Formulas?

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

8. ## Re: Time Card Help - easy Formulas?

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?

9. ## Re: Time Card Help - easy Formulas?

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•