Importing Time/date Data Into Timesheets

Aug 7, 2009

I have been tasked with creating a workbook that will take a time log file (excel) from an electronic time clock and import the dates/time/employee into individual timesheets. The timesheet layout has already been provided to me and cannot change.

Does anyone know how to write formulas that would gather the date range/time in-out/employee information from one worksheet and automatically import it into the appropriate employee time sheet for the week selected?

View 14 Replies


ADVERTISEMENT

Timesheets - Time Arithmetic

Jan 30, 2009

I have a timesheet in excel which details the hours worked per person. It is worked out by have time started in one cell, and time finished in the next cell. (24 hour clock).

The timesheet is for a night club, so people start late, and finish early. Therfore, in the total column I have the following formula...

=IF(D5="",0,IF(D5

View 9 Replies View Related

Multiple Timesheets, Auto Fill From Data Sheet

Jul 28, 2009

I have been tasked with creating a workbook that will take a time log file (excel) from an electronic time clock and import the dates/times into individual employee timesheets. The timesheet layout has already been provided to me and cannot change.

I’m really struggling here and would appreciate any help.

Please reference the attached workbook for the issues described below:

1) Sheet "1_attlog" is the log that is imported from the electronic time clock.

2) Sheet "Sorting & Filtering" is what I have developed so far that manipulates the data from sheet "1_attlog" into something that can be easily understood, it also matches employee #'s to a name, and allows for date ranges to be set that copies over to the individual timesheets. Please do not change any formulas on this page if possible.

3) Sheets "#2 Dan through #10 Blank" are all individual employee timesheets that I need to import the data from "Sorting & Filtering" and populate the "Time In" (column D) and "Time Out" (column G) based on matching the data from "Sorting & Filtering" sheet, "Time Clcok ID" (column D) "Date" (column E), "Time In" (column F), "Time Out" (column G), "Emp Name" (column I).
The dates on the individual timesheets have been linked to the "Date Range" (cels F2 & F3) on the "Sorting & Filtering" sheet, please keep this link intact.

4) The individual timesheets already have formulas inplace to calculate the hours worked after the "Time In" and "Time Out" data has been imported.

View 2 Replies View Related

VBA Importing Data From Access To Excel - Date Format Incorrect

Sep 8, 2008

I am using VBA to export an access query into excel, the query works fine in access but when importing the data into an excel spreadsheet, it doesn't display the date column headers in the correct format.

The problem I have is that the dates 1 to 12 are displayed the wrong way round eg:

In Access date column headers from query are:
09/01/2008, 11/01/2008, 12/01/2008, 14/01/2008, 15/01/2008 etc.

But when exporting to excel, the above dates are shown as:
01/09/2008, 01/11/2008, 01/12/2008, 14/01/2008, 15/01/2008 etc

It seems to be changing round the dd/mm when I export, but only upto 12 when the day is 13 or more it is displayed correctly.

Public Sub bttnDMA_Click()
'DMA Figures in Excel format
'Creating the Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn
Dim MySQL As String ....

View 9 Replies View Related

Convert Imported Date / Time Data To Date / Time Format?

Jan 2, 2013

I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).

View 4 Replies View Related

Excel 2010 :: Insert Date And Time In Column Upon Data Change For First Time Only

May 3, 2013

I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.

Note: If the column I already have the date and time inserted before then it should give message record already have date and time.

I am using office 2010.

View 9 Replies View Related

Date/Time Formula: Pick Up A Date With Time Entry On A Worksheet And Place It Into A TextBox On A UserForm

Jun 17, 2006

I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the

Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub


"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?

View 3 Replies View Related

Timesheets With Day And Night Hours

Jan 26, 2009

I have a timesheet that the manager fill in every night for the workers, simply it has a start time and end time and it calculates the hours worked (all times are entered in quarters of an hour ie 9.25 for 9:15 etc). At the moment i can work out if a person is on day or night with:

View 2 Replies View Related

Automate Calculations On Timesheets (Overtime And Holidays)

Jun 26, 2014

I've been trying to automate the calculations on timesheets and having some trouble. I've attached a rough copy of part of the sheet to this post.

Here's where I'm struggling:

The timesheet has columns for Ins/Outs as well as Reg Hrs (Should never be more than 8), OT 1.5 (Standard 1.5 OT pay for anything over 40 in a week), OT 1.0 (More than 8 hours worked in a day but Vacation/Sick/Holiday/Funeral time keeps it below 40 hours actual work time that week) and then columns for Vacation, Sick, Etc.

Now I'm confident that my Regular Hours works with the following formula:

=IF((((C3-B3)+(E3-D3))*24)>=8,8,((C3-B3)+(E3-D3))*24)

If statement calculates how many hours worked, multiples it by 24 to put it in an hour format and checks whether it's greater than or equal to 8, if so then only show 8 (since any additional hours worked would need to be in the OT 1.5 or OT 1.0 category depending on the circumstances). If false, returns how many hours worked.

However, I am having some issues getting my OT 1.5 and OT 1.0 categories working correctly. The OT 1.5 column needs to check whether the actual worked hours is over 40 without Vacation/Sick/Funeral, etc included. The OT 1.0 column needs to show any additional hours worked beyond 8 each day but less than 40 over the whole week because of holiday/sick/vacation, etc.

So, if I worked 10 hours on Monday and there isn't any sort of paid leave time that week (and I work normal 8 hours the rest of the week), then Monday would show 8 hours in Regular worked and 2 hours in OT 1.5. Here's what I'm currently using for a formula (that isn't working...my latest addition was the OR statement):

=IF(AND((((C3-B3)+(E3-D3))*24)>8, OR(($F$12-$K$12)>40), $K$12=0), (((C3-B3)+(E3-D3))*24)-8, "")

If I worked 10 hours on Monday and there is some sort of Holiday (and I don't actually work over 40 hours that week not including the 8 hours of Holiday pay), then Monday should show 8 hours in Regular time and 2 hours in OT 1.0. Here's what I'm using for this formula (that also isn't working well and I hadn't started working with the OR statement here yet since OT 1.5 wasn't working) :

=IF(AND((((C3-B3)+(E3-D3))*24)>8, $K$12 > 0, $F$12<40), (((C3-B3)+(E3-D3))*24)-8, "")

testsheet.xlsx

View 3 Replies View Related

Calculate Timesheets Including Hours At Overtime

Mar 20, 2009

I'm trying to calculate timesheets including hours at overtime.
A1- Start time, B1- Lunch Start, C1- Lunch finish, D1- Finish
In E1 I want standard rate hours 'up to 8'
In F1 I want the remaining hours.
I have tried IF rules and realise these are wrong.

View 8 Replies View Related

TimeSheets :: Work Out Formula For Working Hours

Dec 27, 2009

Want to work out formula for working hours If Mr Smith is booked to work I must pay him a min of 8 hours pay at rate A then upto 10 hours still on rate A but after that he gets rate B I need to enter his data on a daily spreadsheet

View 9 Replies View Related

Date And Time From Data Table?

Mar 26, 2013

Okay I'm having a mind block on the best way to create a table from user supplied date and time. Once the user updates the date cell and then the time cell it will return only the data specified provided both arguments are true. This data is provided from sheet one where the data is constantly being added to.

View 1 Replies View Related

Counting Data With Date And Time

May 13, 2014

I'm trying to count how many times an action happens between a time frame on a certain date.

This is how my data looks and comes to me (screen shot of a small portion of it - there are 22876 cells to count and it's very time consuming to manually):

data by rld2m2, on Flickr

There is no way to separate the date and time with the program the data comes from.

What I'd like to do is count what how many transactions take place between 10/1/2013 12:00:01 AM and 10/1/2013 3:00:00 AM (example time frame) from the above data.

View 2 Replies View Related

Subtract A Static Date And Time From The Current Date And Time

Oct 24, 2007

I have a column of values resulting from subtracting a static date and time from the current date and time.

This means it is constantly updating, which makes it impossible to sort.

All my work depends on sorting those values, though.

View 12 Replies View Related

Converting Text Date And Time To Serial Date And Time

Feb 21, 2013

I import data from a program that exports dates and times as text. I have been successful using "text to columns" to separate the time from the date and then using =text(A1,"00:00")+0 to get the time to show as serial time but I'd love to be able to do the whole date/time string in one step. In cell A1 there is data that is general format and is in this format:

01/01/13 00001

No matter how you try to format it, it is not a date or time. For this project I need the serial number for the date/time. Any formula that will format it as date/time and then allow it to show as a serial date/time?

View 3 Replies View Related

Convert Date & Time As Text To Real Date & Time

Apr 22, 2008

I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.

View 2 Replies View Related

Auto Date And Time - Input Data

Dec 6, 2013

Auto date and time using excel macro.

In cell B2 until Z2 I am inputting data.

Once the data has been inputted in cell B2, I like to have date and time to have in Cell B6.

If input in cell C2 then cell C6 to have date and time..

View 4 Replies View Related

Separate Date & Time Data Into 2 Different Cells

Aug 10, 2009

I have a cell with both date & time and I need to separate this into 2 separate cells, 1 for the date and 1 for the time. Sample is attached for the before and after formats.

View 3 Replies View Related

How To Lock Down Time And Date When Input Data

May 23, 2012

Is there anyway to lock down the time & date when I input a data? Assuming I have a TIME formula in column A, and a DATE formula in column B. So when I input any data in column C, A & B can automatically show and also lock the time & date.

View 5 Replies View Related

Time And Date Stamp On Data Entry

Jun 28, 2009

I need a function that records the time and date in a cell, say a2, when data in entered in to another cell, say a1 in the same spreadsheet.

This way I can tell when data in a cell in my spread sheet is updated.

View 9 Replies View Related

Add A Date Time Stamp In A Cell After Data Is Entered In A Different Cell And Date

Apr 18, 2014

I am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?

View 9 Replies View Related

Record Time And Date Of Data Entered Into Workbook?

Feb 24, 2012

I'm having trouble while trying to use a variable in a cell reference. I have a sheet that is being used to record the time and date of data entered into the workbook. This sheet is divided into columns, each designated to a specific type of data. I have written a macro to enter the current time in the correct column, but that macro is embarrassingly long and complicated because I used a series of if statements to handle all the different data being entered. I'm trying to simplify by using vlookup, and assigning the column value to a variable, and inserting that variable into the cell reference.

Here is the line I need to insert a variable into:

Sheets("Current").Cells(Rows.Count, columnstart).End(xlUp).Offset(1, 0).Value = Now()

Where columnstart is my variable. It is an integer, but it's value will change depending on the type of data being entered.

View 7 Replies View Related

Remove Specific Time Data From Row To Leave Only Date?

Mar 7, 2014

I have a lot of rows of data in this date and time format "2/29/2013 10:00:25 AM" and I would like to remove only the time and leave the date

View 2 Replies View Related

Evaluate Data By Date/Time, Apply Code

Apr 14, 2006

I have been given a project to develop a spreadsheet using excel that will take data that is for a whole month, and evaluate by DAY to see if the same doctor was listed as attending two patients in surgery at the same time. If the Doctor's name is the same for two patients within the same timewindow for a certain day, a code is applied to EACH RECORD that the doctor was attending in the code field. If there was NO DUPLICATE patient, a different code is applied in the code field.

The tricky thing about this is:
1) Number of records will vary month-to-month
2) There could be 1-xx patient records where the same doctor is listed and there is overlapping time in the SAME DAY OF THE MONTH.
3) The code applied to multiple patients in the code field applies even if the minutes of overlap = 1 minute.
4) Start/End times and length of the patient visits will vary. (Example - Dr. Jones has two patients on the 15th of the month. The start time for patient 1 is 9:00am and ends at 10:15am. The second patient's start time is 9:30am and end time of 11:00am. Under this situation, the multiple patient code would apply)

Does anyone have any experience evaluating by date, then within a time-window to check for record overlap (doctor name in this case)? There could be 200-1500 records per month, and 100/day to evaluate for overlapping.

View 8 Replies View Related

Capture External Data Refresh Date & Time

May 29, 2008

In the Data tab under Connection Properties, I have "Refresh every x minutes" checked. Question is how do I capture in a cell within a sheet the last time it was refreshed?

View 2 Replies View Related

Excel 2013 :: Extract Date And Time Info From A Text Cell That Contains Text And Date And Time

Jul 19, 2014

I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.

THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.

I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.

View 7 Replies View Related

Analyzing Differences In Date And Time Data Presented In Two Columns?

Apr 24, 2014

I am wondering if there is way to analayse date and time data in a way that would allow me to look at time differences.

I have two columns of data, one has the date and time an entry was made onto a client database, the other shows the date and time an amendment or deletion was made.

What I would like is a formula that shows the time period difference between the two entries.

So for example if one column shows 14/02/2014 14:26 and the other shows 14/02/2014 14:28 it would show 1 (minute) as the result.

I have over 1000 entries to look at and manually it is taking a looooooooong time!

View 5 Replies View Related

Excel 2007 :: Data Points - Filtering Date / Time

Mar 15, 2013

I have a set of data points with date and time shown in the picture. I need to take out all the data point that are at the 30 min interval. Meaning I only want to have the data points hourly only. What can I do to remove the :30 minute datas.

I have tried the find option typing In :30 and getting all the points but then have to replace, which I do not want to do. Need to remove them so they do not show up on the trends ( graphs) I made for them.

This is for excel 2007.

Seems I can't get picture up on the ipad, so the data points look like this

1/1/2012 0:00
1/1/2012 0:30
1/1/2012 1:00
1/1/2012 1:30

And so on for an entire year. Just need to remove the ones with the :30

View 12 Replies View Related

Add Time & Date In Current Cell Without Losing Existing Data

Aug 31, 2009

Trying to create a macro that will add the date & time & initials (i.e 8/26/09 2:34 PM JOD) into the current cell.

I've found plenty of macro's that will do this but it ends up deleting any existing text within the cell. I need to be able to add it in the middle of a text string.

View 10 Replies View Related

Entering Date/time Data As Xvalue For Scatterplot With Vba Arrays

Mar 31, 2007

I am trying to create/modify an XY scatterplot using VBA.

I declare local variables and point them to the chart and a new data series for the chart, such as:

Dim chartone As Chart
Dim chartseries As Series

Set chartone = ThisWorkbook.Charts("Chart1")
chartone.ChartType = xlXYScatterLines
Set chartseries = chartone.SeriesCollection.NewSeries()

I set the series data, using an array, such as:

chartseries.Values = Array(1, 3, 5, 7, 9, 11)

I can set the Xvalues to a set of dates by setting .Xvalues to a woksheet range that includes date-formatted data. (like this):

chartseries.XValues = Worksheets("sheet3").Range("m9:m14")
(where m9:m14) contains dates...

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved