Login/Logout Calendar

**ThePhoenix** · 02-22-2018, 02:43 PM

Hello all,

I receive a basic CSV from my time card site for my employees login and logout times.

I have attached an example of what I have so far, Data tab has the export, I created the formulas on that tab to combine/split data and the final data is on Sheet 1.

What I am trying to do is create a new Tab where it shows each agents down column A (2 lines per agent) and dates for the last 30 days (Time card is always last 30 days) across Row 1... effectively creating a calendar that will then pull in their Login time for the day and Logout time for the day.

For example:
Agent 2/22 2/23
Test 06:00 06:01 (Start time of the first line for that date)
Test 14:30 14:35 (End time of the second line for that date)
Test 2 09:00 09:00
Test 2 17:00 17:06

The reason I am wanting to do this is so I can easily just dump the time card data into the data tab so the calendar always updates with the data in that tab or the cleaned tab "Sheet 1" since that pulls from the Data tab.
Once I have the calendar I can then set conditional formatting on the cells for when they are late or early etc.

Thanks for any help you can provide

**ThePhoenix** · 02-23-2018, 01:30 PM

Since there is no response I am wondering if this is not possible? or to complex.

**ben_hensel** · 02-23-2018, 02:46 PM

Like most timecard questions,

This is actually several threads worth of questions, plus some boring table-building.

What you need is:
1) The TRANSPOSE function for the dates across the column headers. (This is entered as an array with in a range, the support documentation is pretty good.) Maybe you can get away with a start date (driven off MIN(range) ?) with dates +1 marching across if you know when your start / end is, that might be easier.

2) Some way to build the headers down the rows for people's names.
This will probably be some variation of marching SMALLIF array formula.
Because you need everyone's name twice, you might need to do something crazier, like the MAX(INDEX(Range*Boolean, 0)) construction, and wrap that with an IF to treat odd/even rows different, that kind of thing.

3) Populating the data.
This will be a lookup; since you've got two values you need for each index (eg, two clock-ins per date), you'll need to do something clever to accommodate that, similar to what you needed to do to handle the above. Just like above, you might be better served to do a combo of MIN/MAX on a boolean test to return the earlier/later values.

4) And then yeah, jiggering that all into a table.

So yeah, there is a lot of complexity here. It's not, like, impossibly complex, it's just... beyond the scope of a forum thread, basically.

**ThePhoenix** · 02-23-2018, 06:07 PM

Hi Ben - Thank you for taking the time to respond, I figured it would be a tough one for me to build but I hit a wall on next steps, at least having the formulas you provided gives me a base point to start from and I thank you for taking the time to share

Login/Logout Calendar

LinkBack

Thread Tools

Rate This Thread

Display

Login/Logout Calendar

Re: Login/Logout Calendar

Re: Login/Logout Calendar

Re: Login/Logout Calendar

Thread Information

Users Browsing this Thread

Similar Threads

Login/logout hrs - is there a quicker way?

Login - Logout report

Login/Logout Report Summary with Late Login/Early Logout Indicator

login and logout time , with total login time ,break> 1 hours

Login Logout Report

Login Logout userform

[SOLVED] First login & last Logout

Bookmarks

Bookmarks

Posting Permissions