excel time

**jrable** · 01-16-2009, 10:16 AM

OK, I have an Excel spreadsheet that will take all of my time punches (entered in mil format) and calculate the remaining time I need to put in to get my 40 hours a week. This was made for those employees on total flex time that calculate minute for minute. On the last day of the week, say Friday, the spreadsheet reveals I have 6.62 hours or 6 hours and 37 minutes left to put in for the 40 hours and it reveals this to the user. I would like to create a formula for another cell that will take the time entered for Friday morning, say 7:08 am (again this is the last day of the week for me to get my 40 hours), then add the 6.62 hours that I need to work and tell me what the actual time of the day will be when I need to punch out and have a zero balance remaining to complete. The end result should reveal 1:45 pm in this scenario.

Thoughts or suggestions?

**NBVC** · 01-16-2009, 10:27 AM

Try:

=A1+B1/24

where A1 contains the start time and B1 the balance hours.

**jrable** · 01-16-2009, 10:39 AM

Thank You. That does give the correct result, in military time, but it also adds the date. I then went to format the cell and change to time only. that sure seemed pretty simple compared to the long formulas needed to get the other results I needed.

Thanks Again

**jrable** · 01-16-2009, 10:41 AM

There is another issue with that though. I need it to only calculate when the Friday morning punch in time is entered. no other times.

**NBVC** · 01-16-2009, 10:43 AM

How about:

=IF(A1="","",A1+B1/24)

**jrable** · 01-16-2009, 11:08 AM

that works, but after i enter the Friday "out punch" then the cell returns the Friday in punch time;

attached is the file to see

**NBVC** · 01-16-2009, 12:12 PM

Then:

=IF(C31="","",IF(D31="",C31+J35/24,""))

**jrable** · 01-16-2009, 12:28 PM

You are awesome! But now look at the same formula for the second 40 hours (G58). it is giving you a time to clock out also if d31 is empty.

**NBVC** · 01-16-2009, 12:37 PM

Are you basing it on D31 or D48? And when should G58 be blank then?

**jrable** · 01-16-2009, 12:47 PM

G58 should be blank until all the in/out punches for both weeks have been entered with the exception of D48. I guess it would be ok if I used just one cell to determine both. for example, if I am recording for week one of the pay period (guess I forgot to mention that the payperiod is bi-weekly), then G58 could tell me when to clock out for the first week. once that calculation is completed and the employee clocks out at that time, it would go blank again until C48 is entered. The real difficulty here, is that this spreadsheet was supposedly designed for two different time calculations. Some employees are required to flex their schedules within the 40 hour week and yet others have the ability to flex within the 80 hour pay period.

**jrable** · 01-16-2009, 12:51 PM

actually, as it stand right now, G58 does go blank again when D31 has a value, so that part is great. But when CD31 does not have a value and D48 does not have a value, then G58 should be blank.

**jrable** · 01-16-2009, 12:53 PM

actually, as it stand right now, G58 does go blank again when D31 has a value, so that part is great. But when D31 does not have a value and D48 does not have a value, then G58 should be blank.

**NBVC** · 01-16-2009, 12:54 PM

Would this formula work in G58?

=IF(SUM(C38:C48)=0,G37,IF(D48="",C48+J53/24,""))

**jrable** · 01-16-2009, 01:02 PM

No it doesn't. i placed the formula in G58. all of the first week punches are recorded. no punches at all recorded for Friday of the second week, yet it tells me I have to punch out at 6:37 am. In this file scenerio 6 hours and 37 minutes are needed to fullfill the second 40 hour week, I'm assuming that is where the punch out time came from.

**NBVC** · 01-16-2009, 01:36 PM

Another IF() added....

=IF(C48="","",IF(SUM(C38:C48)=0,G37,IF(D48="",C48+J53/24,"")))

**jrable** · 01-16-2009, 02:26 PM

same result as before. i think the "J" column formulas are causing the issue?

**NBVC** · 01-16-2009, 02:44 PM

If there are no times in C31 or C48, then both G37 and G58 are blank.

If I enter a time in C31, then only G37 gets filled in.

If I then enter a time in D31, then G37 returns blank again and G58 is still blank.

Now when I enter a time in C48, then G58 fills in with a time...until I enter a time in D48, then G58 is blank again.

What's not right?

**jrable** · 01-16-2009, 03:43 PM

Silly me. I was seeing an amount in G58 when C31 and C48 had amounts. If the user is entering correctly, that instance should never happen and it would be a mute issue.

Thanks for all your help, the formula works perfectly. Thanks again.

**jrable** · 01-16-2009, 03:53 PM

Now ....What If?.....

the employee didn't work the traditional Monday-Friday? Say Saturday - Wednesday or Sunday - Thursday. I know I'm stretchin it, but........

Also, the formula now doesn't take into account if the user leaves for lunch and uses the second set of in/out punches. in the previous file, if the user left today at 10:00 am, then G58 goes blank. Then the user punches back in at 10:30, G58 is still blank.

**NBVC** · 01-16-2009, 03:54 PM

Ok, great.

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**NBVC** · 01-16-2009, 04:09 PM

Originally Posted by jrable

Now ....What If?.....

the employee didn't work the traditional Monday-Friday? Say Saturday - Wednesday or Sunday - Thursday. I know I'm stretchin it, but........

wouldn't you just change the references to the Friday cells to the last day of the work week (i.e. to the Wednesday cells or Thursday cells)?

**jrable** · 01-16-2009, 04:14 PM

yes, if i were to make one for each employee using flex time with different parameters, i was hoping to have a "catch all" if you will.

also see the last posted edited info.

**jrable** · 01-16-2009, 04:17 PM

guess the last edit didn't make it. if the user takes a lunch and uses the second set of in/out times, then it doesn't work either

**NBVC** · 01-16-2009, 04:21 PM

Originally Posted by jrable

yes, if i were to make one for each employee using flex time with different parameters, i was hoping to have a "catch all" if you will.

also see the last posted edited info.

If you enter the ending date code in a separate cell so that the formula can know what date to use, then yes it is possible...

So say you enter FE in A35...then formula in G37:

Please Login or Register  to view this content.

and in G58:

Please Login or Register  to view this content.

**jrable** · 01-16-2009, 04:36 PM

think you lost me there. I entered the string in A35 and immediately got a circular reference error.

**NBVC** · 01-16-2009, 04:38 PM

Originally Posted by jrable

guess the last edit didn't make it. if the user takes a lunch and uses the second set of in/out times, then it doesn't work either

Ok, we are overlapping now....

Still add code in A35... then formulas are now:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

in G37 and G58, respectively, to take into account lunch breaks and possible differing end start/end dates...

**jrable** · 01-16-2009, 04:40 PM

should i have used the same code you wrote for G58 in G37?

**NBVC** · 01-16-2009, 04:44 PM

Use the formulas I last supplied with a code like FE in A35 (which you can move elsewhere later).

See attached for testing...

**jrable** · 01-16-2009, 04:47 PM

OK now you totally lost me. I put the first code in A35. then i put the second code in G37 and G58. do I need to change any of the code for G58 to reflect the second week?

**NBVC** · 01-16-2009, 04:53 PM

You put "FE" in A35... (without quotes)

The first formula in G37

The second formula in G58

Then change FE to WE or TH or whatever you last day of work week is... to see changes in how the formulas work...

**jrable** · 01-16-2009, 04:53 PM

not working. I downloaded your file. I entered 9:41 in D31. that works good.
I entered 7:08 in C48. so far so good, it told me when i need to leave. I entered 10:00 in D48. G58 went blank, that's OK too. I then entered 10:30 in F48, it told me i had to leave at 10:53 am. that would be incorrect. it was originally 1:45 pm when I entered in cell C48, that was right. I left for a half hour and then came back. It should have told me to leave at 2:15 pm

**NBVC** · 01-16-2009, 05:01 PM

Ok.. I used the wrong Index refs in the latter parts of the formulas...

replace formula in G37 with:

Please Login or Register  to view this content.

and replace formula in G58 with:

Please Login or Register  to view this content.

**jrable** · 01-16-2009, 05:08 PM

OK, now it is working. So if I understand you correctly, if some works Saturday through Wednesday, then all I need to do is change A35 to WE instead of FR? is that correct? and the code will automatically pick that up?

**jrable** · 01-16-2009, 05:50 PM

I tried changing the value of A35 and entered values. The program now works beautifully. Thank You. This issue solved!!!!!!!

excel time

LinkBack

Thread Tools

Rate This Thread

Display

excel time

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions