I really need an excel formula that says if a1 is blank, leave blank, otherwise, if c1 is greater than z1 then subtract c1 from b1. (C1, B1 and Z1 are all times (such as 7:00, 6:53 and 7:03)
Can anyone help me?
Katherine
Maybe this, but what if A1 is not blank and C1 is not greater than Z1. I assumed a blank
=IF(A1="","",IF(C1>Z1,B1-C1,""))
Also have you considered that with time, you cannot have negative values. ie. B1 is less than C1
Hi Alan,
You are my hero!! Thank you so much, that worked perfectly! And yes, I want there to be no calculations done if a person clocks in early... just late. This particular spreadsheet is turning me grey!! It's calculating the number of times a person clocks in late, how many minutes late, if they leave early, the number of times they clock in late from lunch, the number of minutes over their 30 minute lunch, number of days absent in a week, number of days worked that were overtime, and the total amount of overtime worked... then all that data needs to turn into a pivot table and chart that can be sorted by employee weekly and monthly. Pray for me!!
Thanks for the feedback. Post here again if you need more assistance with building your PT. Good Luck.
Hi Alan, can you help me with this issue as well?
I have a spreadsheet that I want to have track time on lunch break. D4 is the clock in time for the day, E4 is the clock out time for lunch, F4 is the clock back in from lunch time, and G4 is the formula subtracting the clock out from the clock in times. All of these are shown in the formula bar as 12:00:00 PM for instance but are formatted to show just 12:00 in the actual cell. I have $AU$1 (which shows 0:30) that I am trying to use in a formula that counts the cells that are over 30 minutes from lunch. I'm trying to use this formula but it is also counting the cells that are equal to 30 minutes... can anyone HELP me??? I have all the cells' categories set as "time" and "13:30" if that matters.
=IF(ISBLANK(D4),"",COUNTIFS($G4,">"&$AU$1))
Thank you so much!! ~ Katherine
Suggest you post a sample workbook as an attachment. 8 to 10 records. Then mock up the expected solution for what your issue is. This way we do not have to try and create your issue to find a solution.
