Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Timesheets With Day And Night Hours

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Night Vs Day Working Hours
I have a spreadsheet that calculates the total working hours of our warehouse staff, overtime, etc... using some good advice that has been published here before.

Cell C1 : starting hour
Cell D1 : ending hour

The hours are filled in, using 1904 system, without date as 08:00, 12:00, 21:00, etc... total hours is calculated as follows:

View Replies!   View Related
Calculating Night Shift Hours
I'm trying to calculate the hours worked for both my day shift and my night shift.
Day shift (thanks to search ) I have managed to figure out and worked quite well.
It totals adds up the time and converts it into a decimal of hours worked.

For example Joes starts at 1100 and finishes at 1330 it returns a total of 2.5 hours worked.

However I strike a problem with nightshift.
They start in the late afternnon and work thoguh into the am.
I have used the same formula but it doesn't seem to work:

I assume because once the clock strikes 12 it's a new day and it can't work out the maths.

Lets use the example form about but make it pm.
Joe starts at 2300 and finishes at 0130 it should give me a total of 2.5 hours instead it gives me 21.5 hours

View Replies!   View Related
Calculate Timesheets Including Hours At Overtime
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 Replies!   View Related
TimeSheets :: Work Out Formula For Working Hours
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 Replies!   View Related
Totalling Hours Per Day
On my spreadsheet I'm after a formula for cells E2 and F2 that I can drag down and it automatically populates the cells relevent to the day with the total hours for the Sessions (E2) and the total hours per day (F2). Hope this makes sense, but I'm sure you'll gather want I mean from the spreadsheet.

View Replies!   View Related
Totalling Hours From One Day To The Next
My company works 24/7 and with that some people on nights starting prior to midnight and then finishing after. Of course the "regular" formula works well in calculating between 00:00 onwards but I am getting an error when for instance the person starts at 20:00 and finishes at 04:00. I have tried numerous ways but still the error persists.

View Replies!   View Related
Repeating Numeric Series For Day Hours
In a column, I have a repeating series of 24 different strings corresponding to the hours in a day. The strings are formatted as: 0, 10000, 20000, ... , 24000. Some days do not contain a complete series. What I would like to do is to complete each partial series by inserting a row with the proper hour string and zero's in adjacent cells.

View Replies!   View Related
Day Hour Minute Format To Total Hours
We have a system called Datamart that outputs in excel formatted file.

The output of a duration is : 22.00:8.00:25.00 ( day hour minutes )

I want to be able to add, subtract, average, calculate the 10 fastest/slowests durations from a list of durations in this same format. I have googled and tried custom formatting but excel does not like this format. when I try to sum a range and divide by the number excel gives me 0.

View Replies!   View Related
Hours Worked Based On Multiple IN/OUT Times Per Day
In a single employee Timesheet spreadsheet:


Hrs Worked:.....8.5......8.75. How to format the cells in the daily columns and what formula to calculate 'Hrs. Worked'?

View Replies!   View Related
Table Which Shows Time Started, Time Finished And Then A Total For Hours That Day, Then That Week
i am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.

Start 08:00
End 16:00
Total 8 hrs.

How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?

View Replies!   View Related
Convert 3786 Minutes To Day:hours:minutes
I'm trying to convert 3786 minutes to day:hours:minutes. So divided it by 1440 which is 2.63... but I want this displayed in the worksheet as 2 days 1 hour and 3 minutes (02:01:03), I just can't seem to get it to work and it seems quite simple... but I'm missing something.... I was trying a custom format like dd:hh:mm or [d]:hh:mm and I was also trying a convert function and =day/1440+hour +minute

View Replies!   View Related
Stop Vacation Hours Calculation On Vacation Day
i didn't realize is that my current funcation that calculates vacation hours... will increase after a new year. i'd rather have it not increase until they are "reset" for the new year. how do i stop the function? =VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2)

basically goes to a lookup table with the caculations. PROBLEM: if a user's anniversery date passes, they may go from a 1-2 year status to 3 year bump... this will auto adjust the amount of vacation hours they have. if the reset button is ran to calculate the vacation hours, it might over calculate giving the user 40+ extra carryover hours. anyway i can make this vlookup stop when the current date is or has passed the anniversery date, yet has not been reset? maybe a count down timer, not sure.

View Replies!   View Related
Night Payment Calculator
Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out.

Essentially it's for calculating night payments for our employee time-sheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use.

To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00 - 06:00 where they qualify for night payments but also from 24:00 - 30:00 if that makes sense?

View Replies!   View Related
Timesheets - Time Arithmetic
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...


View Replies!   View Related
Popup Macro, New To Vba, Due Monday Night
What I was told to do was to create an Excel Macro that would record 7 fields via pop-up box. I had no problem with this (see code below) but now I would like to run that same macro for 10 or more students. I would like to have the same data collected (first name, last, email, address, phone, etc) for each student, but have excel compile a list of the recorded data for each individual. Ideally John, Mary, Will, Greg, and Matt would fill out the form and their responses would appear as:

First Last Email Phone Other
John Doe 5551245 etc etc
Mary Jones 5551227 etc etc
Next Student 5551212 etc etc

The macro is started by clicking on a button to register for a free giveaway

Originally Posted by code

Sub RetisterToWin()
Dim FirstName As String
Dim LastName As String
Dim Email As String
Dim MailingAddress As String
Dim PhoneNumber As String
Dim Major As String.............

View Replies!   View Related
Importing Time/date Data Into Timesheets
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 Replies!   View Related
Multiple Timesheets, Auto Fill From Data Sheet
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 Replies!   View Related
Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.

Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.

View Replies!   View Related
Day Of Week -day Of Month List Auto Setup
What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)

View Replies!   View Related
Difference Between Dates And Times In Days , Hours Mins ( Working Hours )
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :
Difference between
02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes


The following displays it in the Hrs and Mins format

=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))

View Replies!   View Related
Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only
Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....

View Replies!   View Related
Time Scheduling: Take Out A 30 Min Break If The Hours Worked Is Over 6 Hours
I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.

I have so far


1 11:00 7:30


Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.

View Replies!   View Related
Format Total Hours To Days, Hours & Minutes
1) The output of an excel duration is : 22.00:8.00:25.00 ( day:hour:minutes ) - excel cannot average and work with this number format

2) resolution - =(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440 as an array and Custom Format the cell as [h]:mm - works perfectly.

Q: to be conistent, the initial reporting is dd:hh:mm and then I convert to hh:mm so that excel can process the data. How can I convert from hh:mm to dd:hh:mm so that the excel report can be consistent in presenting the data to senior management?

example attached.

View Replies!   View Related
Relating A Cell's Day To A Cells Day.
I am trying to relate a specific cells day to a date.
for example:

Today's date is : 11 May 2006
Therefore the day would be: Monday.

I want to link the day cell to the date cell, so when the date changes so does the day.

View Replies!   View Related
Count Hours Between 2 Times Based On Hours In Another Cell
A1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6.

example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted.

If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.

View Replies!   View Related
Working Day Function: (Day + Next 1st Working Day)
I was looking for a final result as follows

21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)

21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)

View Replies!   View Related
Automatically Add One Day To An Existing Day
how can i automatically add one day to an existing day..
1 Monday

in b1 what would be the formula for tuesday..



View Replies!   View Related
Month Day Day Of Week
1 2 3 4 5 6
11/1 11/2 11/3 11/4 11/5 11/6
sat sun mon tues wed thur

what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week


cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.

View Replies!   View Related
Convert Hours To Fractions Of Hours
I am attempting to convert a spreadsheet of times (listed in the format 06:15:39.62, where 06 is the hour, 15 is the minutes, 39 is the seconds, and .62 is in truncated miliseconds) into fractions of hours (so, 6.25 [NOT 6:25!]). I've so far been doing it manually for each value, which is quite tedious (doing basic division of seconds and minutes into hours, to find the fraction) but I'd like a single formula which I can then apply to the whole spreadsheet.

View Replies!   View Related
Convert Second To Hours And Mins (over 24 Hours)
i need to convert second to Hours and mins and can do so using:

View Replies!   View Related
Displaying Sums Of More Than 24 Hours, As Hours.
I am trying to compute a running total of hours (from row 1) in row 2 Example................

As you can see, when the sum exceeds 24 (moving to the right across row 2) the answer resets, so to speak. Cells are formatted as time. This format *seems* incapable of recognizing quantities of hours over 24 except as days, as it were. This is obviously useful in most sorts of cases but not in this sort of case.

If I simply want the aggregate number of hours expressed as such am I doomed to failure whenever the total exceeds 24? In reply to a somewhat similar enquiry elsewhere in this forum, advice was given to format a cell as Elapsed Time. I dont see such a choice in my dropdown menu.

View Replies!   View Related
Find The High Price Day Of Each Week. Then Report The Median High Day
I have an interesting problem (I hope you find it interesting too). I'm trying to find the historical "high" day of the week for a particular stock I follow. I searched for several hours but was honestly unable to make any progress. Normally I do my own formulas, so other than pasting a rare macro, that's pretty much the extent of my (lack of) experience, ie please assume I am a novice....

View Replies!   View Related
Time Conversion: Convert A Time From Hours/Minutes To Hours/Tenths
When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?

View Replies!   View Related
Day ## Of ## Counting
I am looking for a formula that will count days in increments of 1 through 40. I need it to look like this: DAY 1 of 40

Whereas only the "1" counts up to 40 every 24 hours. And maybe a button to reset the counter back to "1" ...

View Replies!   View Related
Use NOW() Without Current Day
how I can use the NOW() function without the date....just the time?

I need it so that if I format the cell and chage to general, it will show 0.xxxxx

rather than 39230.xxxxx

View Replies!   View Related
Month And Day Only
I want to compare dates in two columns but I want to exclude the year.

For example:

column A 08/15/2002

Column B 08/21/2008

Column B - Column A = 6 days

View Replies!   View Related
Adding A Day
I have excel 2007. I am trying to do the EASIEST thing in the world and it just not working for me. Basically all I want to do is add days to an inputted date in a cell.

Works great if there's a date already in the referenced cell (A1), however, if I input a new date it doesn't re-calculate.

I have tried:


and =DATE(YEAR(B1),MONTH(B1),DAY(B1)+34)

Not recalculating when in enter in a different date into B1, why? How do I fix this so it will work correctly?

btw....basically I want to have a whole column (B) of the date formula so when I enter a date into cells in column A, it calculates.

View Replies!   View Related
Day Of The Week
I need the name of the day of the week from a date that has to be put together. This is going into a label on a UserForm.

I have a named range called wbDate. It is the month & year. The day of the month is the value of a TextBox (TBDay) on a UserForm. When I tried to combine these items, I used this

vToday = Month("wbDate") & " " & TBDay.Value & ", " & Year("wbDate")

vToday is dimmed as variant.

I am getting a type mismatch error and I'm not sure how to correct it.

What I am aiming for eventually, is the name of the day of the week.

View Replies!   View Related
First Day Of Week
How can i find the first day of a week

I have the week number and i need the date of monday of that week

Ex: Week number:24
First day of week(Monday):12-06-2006

View Replies!   View Related
Adding Hours
I have two employees and need to be able to add the hours each works so I have a daily running total of their hours together (the two employees can't work more than 30 hours together each week).

What do I put in F5 so I can easily read that, together, they have so far worked 7 hours and 24 minutes? How do I format the cell: time? which time? numbers?

02/15/102:12 PM5:37 PM3.423
02/15/102:12 PM5:37 PM3.424


Total Hours:

View Replies!   View Related
Count Hours
How to count the hours using Excel cells (for time table scheduling)....for example : 11:00-9:00 is 10 hours, "OFF" is 0 hour, "X" means 0 also, 10:00-7:00 is 9 hours, and so on. But when I try to put the hours, they show ###### in the cells? I know the time table is mixed with numbers and letters, that's why I'm confused to use any formula to do it, but my boss told me NOT to change the original format what so ever.

View Replies!   View Related
Calculate Hours
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2-D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is -4 hours.

View Replies!   View Related
Calculate The Hours
How to calculate the Hours
Check the attached file

View Replies!   View Related
Shifts Hours..
In the attached WB - the formula, in Col. "D", seems to be too long - although returning the expected result. Could somebody lighten my eyes with a much shorter formula ?

View Replies!   View Related
Converting Hh:mm:ss To Hours
convert hh:mm:ss into hours, that is 90:10:20 to 90.1722

View Replies!   View Related
Time, Day And Criteria
What it does is populate a graph based off the time frame of multiple incidents occurring on a specific day.

My goal: To have Sheet2 tables populate the times automatically from Sheet3 based off set criteria.

Basically what I need is a formula in the Begin Time and End Time cells of the graph for Sunday through Saturday on Sheet2. I want this formula to pull the lowest time and the highest time from raw data on Sheet3 based off criteria in B2 and D5 on Sheet2. I need for days that Criteria in D5 is not met, to return blank. This way it maps out the overall time of all incidents for criteria in D5 and places the time frame on the days that criteria in D5 is shown.

So for example if Sunday, Monday, Wednesday are the days that "Case 3" (Sheet2 D5) shows up on Sheet3 raw data. I want the total time from all incidents containing Case3 to populate on only Sunday, Monday, Wednesday, and leave all the rest blank since Case 3 does not show on those days.

Since the times on my graph all end in "00" is there a way to round down the time to end in "00". So if it comes back as 11:50 can the formula round down to 11:00?

View Replies!   View Related
Evaluating The Day Of Week ?
Can somebody have a look at the workbook that I've attached.

I have set up a timeshhet calculator for work but the thing is, the hourly rates of pay are dependent upon the day of the week the Operative is working, and the time of the day.

We split the shifts AM & PM and the rates are decided as per the worksheet contained within the workbook "Rates".

The problem I am having is how to get the timesheet to understand the days.

For example, if an Op works the Sat night or Sun night, then the rate should reflect x1.5. If it's Sat/Sun daytime, then it's x1.25.

Again, and as per the "Rates" sheet (see top for the rate multiplier) Mon - Fri 09:00 - 17:00 is flat rate/standard hrs. If the work Mon - Fri evenings, then the rate is x1.25.

How can this be done?

View Replies!   View Related
Adding Year Plus Day
I have a formula =if(AA2="","",edate(AA2,12)) which is working well for the Year

This is a delivery run thats requires that the pick up will aways be on a Monday.
I would like the exact day for the following years.

Monday 2009 is the 9th March
Monday 2010 is the 8th March
Monday 2011 is the 8th March
Monday 2012 is the 6th March
Monday 2013 is the 4th March

I would appreciate it if it is possible or a alternative way of doing it.

View Replies!   View Related
Cell Shading By Day
I have two columns. Date and Day

Date Jan 1, 2007 and it falls on on a Monday in the Day column.
Date Apr 6, 2007 and it falls on on a Friday.
Date Dec 25, 2007 and it falls on on a Tuesday.

What I want is to Colour Code the DAY column in case it is a holiday falling on a Monday or a Friday.

View Replies!   View Related
Last Day Of Month Bold
Col. A are dates,I would like to format Cells so the last day of the month is bold useing conditional fomating

View Replies!   View Related
Copyright © 2005-08, All rights reserved