Shift Hours Calculation

Jan 22, 2010

I have enclosed a sheet with the dilemma i currently face.

Ive tried multiple variations on a solution none of which have been 100% accurate.

Basically the work day is split into 3 shifts :

Days ( 06:00 - 14:00 )
Afters (14:00 - 22:00 )
Nights ( 22:00 - 06:00 )

I have a report which tells me the total time the colleague will be getting paid for and there
clock in and out times.

I need to determine which shift bracket there hours fall into based on the time bands.

Ie :

David worked 8 hours , started at 10:00 finished at 18:10 , so thats 4 hours recorded in days and 4 in afters since he worked across both shifts. the 10 minutes is not being paid so it doesn't need to be recorded.

the sheet should explain things better.

ADVERTISEMENT

Calculate Pay For Shift Work With Different Rates Based On Shift Hours

Apr 11, 2008

a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................

View 8 Replies View Related

Looking For A Formula That Can Separate Day Shift Hours From Night Hours?

Aug 6, 2013

I have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?

E10 = 19:00 or start of night time hours
B3 = start time
C3 = end time
D3 = day hours workeds =24*IF(E10

View 1 Replies View Related

Calculating Hours Worked For 3rd Shift

Nov 27, 2012

Weekly Timesheet.xlsx

This spreadsheet calculates hours worked great for first and second shift but when you enter times for third shift it goes all whacky with the outcome.

View 10 Replies View Related

Calculating Night Shift Hours

Jan 15, 2009

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.
=ROUND((E7-D7)*96,0)/4
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:
=ROUND((K7-L7)*96,0)/4

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 9 Replies View Related

Timesheet Calculation For Shift Workers?

Jun 23, 2014

As per attached spread sheet in the yellow highlighted cells, I am trying to input a formula to automatically calculate the hours between specific times for shift workers so I can easily calculate their penalty rates however I just cant seem to get it to work, the main issue being midnight.

View 9 Replies View Related

Calculate The Number Of Hours In A Shift After Midnight

Jan 11, 2010

how would I calculate the number of hours in a shift after midnight. What I want to say is that the number of hours after midnight on a friday shift to be taken off from friday total hours and added to a saturday shift.

View 9 Replies View Related

How To Find Out The Number Of Hours Of Minutes Work Per Shift

Feb 3, 2014

I would like to find out a way to work out the number of minutes worked during particular shifts for weekdays. Basically I have two columns, one for start time, and one for end time. They are formatted like dd/mm/yyyy hh:mm. So they have the date in there as well.

I would like a formula that would look at a range say A1-A11 and work out what shift it is and then output number of hours worked per shift. Day shift would start at 8am and finish 5pm, Twilight shift would start at 5pm and finish at 9:30pm, night shift would start at 9:30pm and finish at 8am the next day. So I would need it to check for example the start and end times (and dates) and then output 3 rows that show the total minutes worked.

There will be multiple days so it would need to say for example Monday Day, Twi, Night, Tuesday Day, Twi, Nights etc. Up to Friday Day shift because we don't work Friday Twilight or Nights, and we don't work Weekends.

Basically there is a list of jobs completed with Start Time and End Time for each and I also have a column that works out the number of minutes worked on that job. So the formula would need to look at many rows.

View 7 Replies View Related

Calculating Day And Night Shift Hours From Date / Time

Dec 22, 2009

I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.

Note that the night shift carries over to the next day.

Start/Finish
21/12/09 07:00 to21/12/09 11:09
21/12/09 07:46 to21/12/09 14:41
21/12/09 12:13 to21/12/09 22:08
21/12/09 16:40 to21/12/09 18:05
21/12/09 19:40 to22/12/09 02:34
21/12/09 23:20 to22/12/09 04:39
22/12/09 02:06 to22/12/09 06:15

View 9 Replies View Related

Calculation Of Shift Allowance Affected By Overtime And Overnight Shifts

Apr 21, 2014

I have an Excel sheet where users enter shift start and finish times (normal Excel time format) - for example: A1 might be 18:00 and A2 might be 06:00 for an overnight shift from 6pm to 6am.

All I need to calculate from these times are the number of hours to which an allowance applies, under the following conditions.

The allowance is paid for all hours worked between 18:00 and 06:00.After 10 hours, a shift becomes overtime and no shift allowance is paid.Shifts are regularly worked overnight (i.e. past midnight into the following morning)

Example scenarios include:03:00 - 15:00 would pay 3 hours of shift allowance09:00 - 21:00 would pay 1 hour of the allowance (as the shift becomes overtime from 19:00)03:00 - 21:00 would pay 3 hours19:00 to 08:00 would pay 10 hours (as the shift becomes overtime from 05:00 the following morning)22:00 to 10:00 would pay 8 hours
And so on.

I feel like I’ve got most of the pieces of the jigsaw, but I can’t put them together! I’ve got the following formulae working out bits of what I think I need:

[Code] ........

The above works out the number of hours before 6am and after 6pm respectively (which I can then SUM), and I believe also accounts for overnight shifts. This obviously doesn't include the more-than-10-hours criteria yet.

The larger formula now looks like this:

[Code] ......

…but this still doesn’t work properly! The sections referring to 1.25 were my attempt to get the shift allowance to stop if the past-midnight shift continues past 06:00, but I don't think it works properly.

I also know I’m probably using *24 and /24 more than I need to, but that’s partly so I’ve got a better grasp of what the formula is doing.

Once this is working, I'm happy using an IF… >10 formula to prevent the total number of hours of shift allowance being more than 10. However, I’m really struggling to find one single formula that will factor in shifts that might start before 6am and finish after 6pm (i.e. 05:00 – 19:00, which should pay one hour), shifts that might go past midnight and possibly past 06:00 the next day, and so on.

Lastly – not to try and complicate things further – there is an optional cell elsewhere, say A3, where a user enters ‘Y’ if the individual takes an unpaid 30 minute break at some point during the shift. This is to be deducted from whatever type of hours are being paid at the end of the shift. For example, if a shift is from 01:00 - 11:00 with the break, it would pay 5 hours with a shift allowance and 4.5 without. If the shift was 13:00 - 23:00, it would pay 5 without the allowance and 4.5 with the allowance. Is there a practical way of doing this, or does this become much more complicated?

View 1 Replies View Related

Operational Model - Shift Start Time Plus Amount Of Hours Worked In One Cell.

Feb 19, 2009

Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that?
For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell).

Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )

View 5 Replies View Related

Calculation Hours Worked?

Jul 3, 2014

I'm trying to make a way to track if I've worked more or less than the 39 hours/week I'm paid for. At the end of each week, I have a total of how many hours and minutes that I've worked .

On column C I have what I should work.

On column D I have what I did actually work

On column E I'd like to convert automatically Columd D to minutes for calculation purposes

Column F to know if I worked more or less than what I should've subtracting C and E

Column G to have an ongoing tally to know if I need to work more or less

Column H and I could probably be the same thing. Ideally what I'd like is to have a formula pull the information from column G and put it into workdays, hours, and minutes with 1 workday being 7 hours and 48 minutes.

Since I tend to work too much, I'd like to know if I've worked 3 days too much during 1 month, I can take 3 days off the next month to get everything zeroed back to where I don't owe the company anything and vice versa.

View 6 Replies View Related

Total Hours Calculation

Oct 22, 2008

I need to calculate below hours
07:50
07:50
07:50
07:50
07:50

The answer suppors to be 37:30 Hours but its showing total diffrent value. i used sum(E1:E5) Excel formula, but its not working.

View 10 Replies View Related

Hours Worked Calculation ....

Dec 2, 2008

I am trying to create a spreadsheet that auto calculates my emp. time.

However I do not want to use military time. I can get it to work by =a2-a1 but only if it is 8.5 and 17.5. Any ideas how I can do clock in 8:30 clock out 4:30 = 8 hours?

View 7 Replies View Related

Calculation Of Business Hours

Mar 31, 2009

I am using the following formula to calculate business hours.

=(NETWORKDAYS(R9,T9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(R9,T9),MEDIAN(MOD(T9,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(R9,T9)*MOD(R9,1),"17:00","08:00")

The business hours considered here is 8AM - 5PM, Start time in R9 and End time in T9. Now the problem is its calculating the correct value when the days are same, for e.g.,

Condition 1

When I am giving "31 March 2009 15:00:00" as start time (R9) and "31 March 2009 23:00:00" in end time (T9), I am getting the correct value. i.e, "2:00:00"

Condition 2

While giving "31 March 2009 16:00:00" as start time and "01 April 2009 09:00:00" as end time I am getting a value of "1:00:00", actually the value should be "3:00:00".

View 5 Replies View Related

Hours To Days Calculation?

Feb 5, 2013

I am trying to figure out the calculation of cycle time from my worksheet. I have 1419 hrs equals 59 days 03 hrs.

If A2=1419

I tried this formula

=INT(A2/24)&" days " &MOD(A2,24)&" hrs"

Which gave me an answer of 59 days 3 hrs.

I was wondering how they got "03 hrs"?

View 9 Replies View Related

Flying-Hours Calculation

Jan 22, 2010

One of my administrative duties is to keep a record of all of the flying-hours completed by a group of twenty pilots. I've constructed a spreadsheet and entered all of their flying records into it.

At the head of each column I have the date, aircraft type, registration number, pilot name, co-pilot name, other crew name, day flying, night flying, solo, dual, total captain hours, etc.

Whilst that I've completed the easy part of this project and that I can transfer each individual pilots flying-hours into his own seperate logbook (by filtering and copy/pasting into another worksheet), there are three other reports that I'm required to provide:

1. To be able to list the number of flying-hours completed during the previous 7 days (for each individual pilot).

2. To be able to list the number of flying-hours completed during the previous 30 days (for each individual pilot).

3. To be able to list the number of flying-hours completed during the previous 90 days (for each individual pilot).

View 9 Replies View Related

Removing Non Work Hours From Calculation

Mar 9, 2006

I am trying to work out the minutes elapsed for a call monitoring
system. The hours monitored are between 05:30 and 19:00 - so if a call
gets logged outside of these hours then the minutes calculated will be
calculated from 05:30 the same day if logged on or after midnight or
05:30 the next day if logged before midnight (ie the next 05:30).

View 22 Replies View Related

NIGHT Working Hours Macro Calculation

Sep 29, 2012

I would like to write a macro that can return the number of hours worked during the night shift.

In this case, the night shift starts at 21:00pm and ends at 6:am next day.

The column "I2" returns the number of hours worked during the day - formula ((F3-C3+(F3<C3))-(E3-D3+(E3<C3)))*24);
The column "J2" gives the overtime hours taking into account the number of regular hours allowed - IF(OR(I3="",I3<6),"",IF(I3>H3,"",I3-H3));
Cells "C3:F13" allows users to set up (using a data validation list) starting time, lunch in, lunch out, end time ( columns C and D AM; E and F PM);

My question is: How can I calculate night hours in column K, without having conflits with numbers returned in column I (worked hours)? Is it possible to write a macro for this?

Below please see the table:

B
C
D
E
F
G
H
I
J
K

1
Name
Start Time
Lunch In
Lunch Out
End time
BREAK?
Regular hours
Worked Hours
Overtime
Night Shift

[Code] ......

View 1 Replies View Related

CALCULATION TAKING BUSINESS HOURS INTO CONSIDERATION

Feb 18, 2004

I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete.

I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am-5pm and closed on Saturdays and Sundays.

here are some examples.

from - 2/12/2004 13:00 (thursday)
to - 2/13/2004 9:00 (friday)

answer should be 5 hours

from - 2/13/2004 14:00 (friday)
to - 2/16/2004 10:00 (monday)

answer should be 5 hours

View 9 Replies View Related

Calculation Of Business Hours Excluding Sundays And Holidays?

Jan 29, 2014

I want to calculate the time in hours between to dates excluding non-working hours and sundays and holidays.

My office timings: 10:00am to 05:30pm Saturday is working day. Only sundays and holidays to be excluded.

View 8 Replies View Related

Time Calculation: Calculate How Many Hours Have Elapsed Between To Entries

Jun 22, 2009

I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10-H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10-H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.

View 3 Replies View Related

Calculation Of Hours Based On Reference (time Format)

Jan 7, 2012

For the past month now, I'm trying to calculate some work hours (night hours actually), based on a reference.

Let me show you the table:

- Column A has all of the schedules of the employees
- Column B has the numbers of hours for each schedule (all have 8.5 hours/day)
- Column C has the reference for the night hours.

Now what I'm trying to do, is to find a formula that will calculate the numbers of the night hours using the reference in Column C

Row 23 in that table has an example of what I'm looking for.

View 9 Replies View Related

Overtime Calculation For Time Worked Over 8 Hr Per Day Or Over 40 Hours Per Week?

Aug 4, 2013

My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.

View 9 Replies View Related

Date/Time Calculation For WeekEnds/After Business Hours

Jul 23, 2007

What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends.

eg.

Date 1 Date 2 Result Time
23/07/07 8:00 24/07/07 14:55 15:55

So far I can't get the caluclation that will compensate for the after hours and week ends.

View 9 Replies View Related

Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)

Dec 1, 2009

I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.

I have attached a screen shot of the current page,

In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.

The shift pay is the column i am having trouble with.

I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.

View 3 Replies View Related

Separate Simple Hours / Holiday Hours And Night Hours

Jun 27, 2014

As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.

Nights, holidays and holiday night have different price/hour.

The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…

On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!

Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)

View 1 Replies View Related

Stop Vacation Hours Calculation On Vacation Day

Nov 11, 2009

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. http://www.ozgrid.com/News/excel-eva....htm#ExcelTips

View 3 Replies View Related

Start Shift And An End Shift

Nov 18, 2009

I have a Start Shift and an End Shift time,

Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs

I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.

View 12 Replies View Related

Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours

Nov 4, 2008

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 9 Replies View Related

Calculate Hours Worked With Lunch / Overtime / Holiday / Vacation And Saturday Hours?

Jan 13, 2014

Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.

For accounting and payroll purposes, we need the totals to display in both hour and decimal format.

So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.

View 2 Replies View Related

Difference Between Dates And Times In Days , Hours Mins ( Working Hours )

Mar 2, 2010

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

=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))

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 9 Replies View Related

Adding Hours Worked Minus Lunch Break IF Over 5 Hours?

Dec 15, 2013

I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.

View 3 Replies View Related

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only

Jan 29, 2010

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....

View 9 Replies View Related

How To Calculate Hours Between 2 Or 3 Days Exclude Non-Working Hours

Dec 29, 2013

I'm having difficulty to calculate hours between 2 or 3 days exclude non working hours.

Attached is the example of start date with time & end date with time.

The situation is like "when the case log in till the case assist in working hours." so i will get the hours from case log to case assist.

Testing.xlsx‎

View 8 Replies View Related

Calculating Actual Hours Worked Only In Core Hours?

Aug 11, 2010

I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.

View 3 Replies View Related

Calculate Hours Between Date And Time Within Business Hours?

Apr 4, 2013

find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?

08/03/2013 13:32:00
02/04/2013 09:32:50

View 4 Replies View Related

Count Hours Between 2 Times Based On Hours In Another Cell

Jun 11, 2008

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 9 Replies View Related

Time Scheduling: Take Out A 30 Min Break If The Hours Worked Is Over 6 Hours

Jan 13, 2009

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

A B

1 11:00 7:30

=24*(B1-A1)

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 9 Replies View Related

Format Total Hours To Days, Hours & Minutes

Aug 30, 2007

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 9 Replies View Related

Convert Hours To Fractions Of Hours

May 28, 2009

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 2 Replies View Related

ADVERTISEMENT