Timesheet Calculating Regardless of date and am/pm

Anne Troy · Jun 4th, 2002, 05:51 AM

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' · Jun 4th, 2002, 06:40 AM

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 · Jun 4th, 2002, 06:48 AM

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 · Jun 4th, 2002, 07:03 AM

Quote:

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 · Jun 4th, 2002, 07:22 AM

Doh!

PDuPre' · Jun 4th, 2002, 07:31 AM

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 · Jun 4th, 2002, 07:51 AM

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' · Jun 4th, 2002, 08:58 AM

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 · Jun 4th, 2002, 09:20 AM

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 · Jun 4th, 2002, 09:54 AM

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.

Jun 4th, 2002, 06:48 AM	#3
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,535	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. __________________ ~Anne Troy

Jun 4th, 2002, 07:22 AM	#5
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,535	Doh! __________________ ~Anne Troy

Jun 4th, 2002, 09:54 AM	#10
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,535	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. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Jun 4th, 2002, 05:51 AM	#1
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,535	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!!

Jun 4th, 2002, 06:40 AM	#2
PDuPre' Board Regular Join Date: Apr 2002 Location: Trussville, AL Posts: 134	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

Jun 4th, 2002, 07:31 AM	#6
PDuPre' Board Regular Join Date: Apr 2002 Location: Trussville, AL Posts: 134	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

Jun 4th, 2002, 07:51 AM	#7
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,535	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!!

Jun 4th, 2002, 08:58 AM	#8
PDuPre' Board Regular Join Date: Apr 2002 Location: Trussville, AL Posts: 134	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

Jun 4th, 2002, 09:20 AM	#9
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 51,549	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 ]