Time Calculation and formulas needed (2 questions)

V

--Viewpoint

#1
I'm creating a time sheet and set it up for time in and out to have separate
cells using the h:mm AM/PM format. In a separate cell I have amount of time
taken ro lunch. The cell for entering the calculation is set up to convert
to decimal format rather than time.

I do not know how to complete the following formula so that it recognizes 5
hours:

=IF(F10-E10>5 hours, F10-E10-A10, F10-E10)*24

Goal: I want to subtract lunch time if I work over 5 hours, otherwise Iwant
to know the hours worked if under 5 hours)

#2 I think I need an array formula (within one cell):
If cell I10>0, V, ""
If cell J10>0, S, ""
If cell K10>0, P, ""
 
T

Teethless mama

Ans #1:
=(((F10-E10)>--"5:00")*-A10-E10+F10)*24

format as General

Ans #2:
=IF(I10>0,"V",IF(J10>0,"S",IF(K10>0,"P","")))
 
V

--Viewpoint

I am not getting the correct result with the formula you provided for
question #1. It is likely that I wasn't clear in my first post so I'll try
to provide a better description. I really appreciate your help, so I hope
you will have patience with me.

Time in: 7:30 am
Time out: 5:30 p.m.
Lunch: 30 minutes (or whatever time I enter).

If we work 5 hours or more, it is mandatory to have a lunch break, but the
break could be minutes or hours!

Columns set up in the worksheet:
Time In (cell E10)
Time Out (cell F10)
Lunch (I'm using time format in this cell, is that correct?) (cell A10)
Actual hours worked (cell B10 is where I'm entering the formula)

If more than 5 hours worked: Time out - Time In


The formula you provided for question #2 worked perfectly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top