Timesheet Calculating Regardless of date and am/pm

[Anne Troy]

I posted this question, and now cannot find it. Let's see if it's a conspiracy.

I need a formula that will calculate the following cells, as indicated, regardless of the am/pm or date value that the entry has picked up. The entry is displayed only as h:mm and we don't want to make the users enter more than the time to differentiate between am/pm or date.

A1: Time in (no total shows)
B1: Lunch out (time between a1 and b1 shows)
C1: Lunch in (time between a1 and b1 shows)
D1: Time out (total time between a1 and b1, plus total of time between c1 and d1)

Hope this is all the info you need, if not, let me know.
THANKS IN ADVANCE!!

[PDuPre']

OK. So everything will work fine for you if the user enters their time with AM or PM after it. But, the object is to have them enter the time without AM or PM right?
Difficulty being how should the default change to know that 4:30 is actually 4:30 PM because the prior entry was 12:30 PM and not 12:30 AM. (Assuming you have 3 shifts of users)
Sounds like you will need the user to enter the data into a vba code which will then assign the AM or PM based on previous entries. I think there's a potential for entry error depending on people's shifts.
Philip

[Anne Troy]

Thanks. I'm not at all opposed to a VBA solution! Might need a little help carrying it out...not sure. I don't WRITE code.

[Chris Davison]

On 2002-06-04 05:51, Dreamboat wrote:
I posted this question, and now cannot find it. Let's see if it's a conspiracy.

Dreamboat.... it's in the MVP section

[Anne Troy]

Doh!

[PDuPre']

If your answer is in the MVP section that's great. I can logically think of what I want to code, but I'm not able to drop it in yet. Thinking that the time entered will always have to be after the previous time. Logically it seems pretty simple. Problem would occur if someone works for more than 12 hours without a lunch break.

Philip

[Anne Troy]

LOL, Philip. My *missing post* was in the MVP section, not the answer.

But I've got a formula solution to share:

=IF(OR(LEN(B4)=0,LEN(C4)+LEN(D4)+LEN(E4)=0),0,IF(AND(LEN(C4)*LEN(D4)=0,LEN(E4)>0),NoLunch,IF(AND(OR(LEN(D4)=0,LEN(E4)=0),LEN(C4)>0),Morning,Morning+Afternoon)))

And it's really ugly, isn't it!!

[PDuPre']

Dreamboat,

Try =IF(B10="",IF(D10="",0,IF(E10="",0,Afternoon)),IF(C10="",IF(E10="",0,NoLunch),IF(D10="",Morning,IF(E10="",0,Morning+Afternoon))))

Assumes each cell is blank if no time was entered. This will give a time if the employee comes in in the afternoon, but not the morning.
I like how you add the Len(C10)+Len(D10) etc. Never tried that.
Note: where I test for ... E10="",0 ...
the 0 could be replaced with "ERROR" if that will work. That would highlight that they need to input an exit time in order to get paid for that day. Or, could assume a standard day depending on employee type, etc.

I used this in conjunction with data validation that requires each time entry to be after the previous time entry. It will not allow 4:30 AM to be entered if the employee arrived at 7:00 AM. Forces the user to enter either 16:30 or 4:30 PM.

Philip

[Aladin Akyurek]

The entry is displayed only as h:mm and we don't want to make the users enter more than the time to differentiate between am/pm or date.

What follows is an attempt to answer the am/pm issue.

In time-formatted A2 enter:

8:30 [ which reads on the formula bar as: 8:30:00 AM ]

In time-formatted B2 enter:

2:30 [ which reads on the formula bar as: 2:30:00 AM ]

In C2 enter:

=(B2 > A2)*(B2-A2)+(B2 < A2)*(B2+(B2 < A2)-A2-"12:00")

Time-format the formula cell. Note that this is an "improper" way of handling time in Excel [ Hope I don't get annotated for proposing a scheme of abuse. ] However, you can also text-format and right-align the time-entry cells (A2 and B2 above). The formula should still work.

Note. The underlying presupposition is that B2 comes later in time than A2.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-06-04 09:24 ]

[Anne Troy]

Thanks anyway, Aladin, but I don't know how to integrate it into my formula that works on all four cells.

Philip: The formula works GREAT. Any way we could have it show the morning hours after the *lunch in* time has been entered? Currently it shows no hours.