Min/Max Timesheet Help

**oebayo** · 01-03-2011, 11:26 PM

Hello and happy new year to all!

I've having the most difficult time trying to come up with a formula that will calculate the min/max for login/logout times.

I have attached a worksheet that will allow a user to paste exported time clock data, but the difficulty is when I'm trying to summarize the daily activity, it would return the min/max range for the entire column.

In a nutshell, each employee may have more than one login or logout for the day and I want to be able to take the min/max for that one day and record it.

I figured it was a time formatting issue, but I've tried and tried and I'm losing hope. Hope you guys can help.

Thanks!!

**MarvinP** · 01-03-2011, 11:40 PM

Hi oebayo and welcome to the forum,

I don't understand your example file. If you could make it a little simpler and explain what logins and logouts your are using it would be easier to give a better answer.

Also could you update your profile as it says you are using Excel 2003 but are showing an .xlsx file. That doesn't make sense to me either.

**6StringJazzer** · 01-03-2011, 11:50 PM

You have a couple of eccentric things going on here and there may be some confusion about how these functions work.

In column C you have

=IF($E$1=$D$2:$D$1599,MAX($E$2:$E$407),"")

The highlighted part will always give you the maximum time in the range $E$2:$E$407. This will be the latest clock-in time for the whole collection of days, which it is doing correctly. You seem to be expecting it to do something else. You have a similar situation in column B.

The other highlighted part works but is a bit unusual since you could just use this simpler formula in C4, for example, and copy to other cells in column C:

=IF($E$1=D4,MAX($E$2:$E$407),"")

Also, the organization of your data seems needlessly complicated, because I see so much duplication. It's hard to tell what you're trying to do.

**oebayo** · 01-03-2011, 11:57 PM

Hi Marvin -

Sorry for the confusion. I have Excel 2007 at home and Excel 2003 at the office. Should be the other way around, eh?

Let me see if I can clarify (please let me know if you need other details).

The data in column D is a concatenation of the name & date utilized for a lookup for a SUMIF function on another worksheet (summary).

This login/logout worksheet is linked to the summary worksheet so that the time data would pull up an employee's stats for a specific date.

Since this worksheet will contain multiple employees with multiple logins and logouts on various days, I'll need to create a master spreadsheet to find the min/max for a specific date query..limiting it to the lowest and highest login for that single day.

I also apologize for the confusing formulas within the spreadsheet. I left it in there to show what hasn't worked for me.

------
The table on on columns S to V should let's say bring up data for John Doe from 11/15/2010 to 11/18/2010

**oebayo** · 01-04-2011, 12:04 AM

Also, are rewards allowed on this forum?

**MarvinP** · 01-04-2011, 12:19 AM

Hi,
I've rearranged your data a little and done, what I think you want, using a pivot table. Pivot Tables allow displaying the Min and Max. I also found some problems with your Login times as they were text and not times. Timevalue(your text) fixed that. See if this gives you some ideas.

**MarvinP** · 01-04-2011, 12:21 AM

Rewards? If you click on the scale icon above a message you can thank us for our help. If you don't like the help you can give us demerits also.

**oebayo** · 01-04-2011, 12:47 AM

Looks good, but is it possible to do it without the Pivot Table?

I'm willing to Paypal $10 to the first person to come up with the solution. :-D

Min/Max Timesheet Help

LinkBack

Thread Tools

Rate This Thread

Display

Min/Max Timesheet Help

Re: Min/Max Timesheet Help

Re: Min/Max Timesheet Help

Re: Min/Max Timesheet Help

Re: Min/Max Timesheet Help

Re: Min/Max Timesheet Help

Re: Min/Max Timesheet Help

Re: Min/Max Timesheet Help

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions