How to Calulculate Hrs and Mins between time frames

This is a multi-part message in MIME format.

------=_NextPart_000_0293_01C68406.3D280160
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

If i have 2 cells with time values say: A1=3D7:30AM and B1=3D3:30PM.

7:30AM - 3:30PM set to NORMAL Hours

3:30PM - 6:30PM set to TIME & HALF Hours

Any time after 6:30PM - 7:29AM to be DOUBLE TIME hours

Then if i set 4 other cells for data input:
A2=3D( DATE 1) 29/5/2009 Start date.          B2=3D(Date 2) 29/5/2006 =
Finish date.
A3=3D(Time) 7:30AM          Start Time.         B2=3D (Time) 7:00PM =
Finish Time.

How can i set 3 Cells say (C1:E1) to display from the above example :
C1=3D8 hrs (NORMAL TIME)
D1=3D3hrs (Time & HALF)
E1=3D2hrs (DOUBLE)

???
------=_NextPart_000_0293_01C68406.3D280160
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2873" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>If i have 2 cells with time values say: =
A1=3D7:30AM=20
and B1=3D3:30PM.</FONT></DIV>
<DIV><BR><FONT face=3DArial size=3D2>7:30AM - 3:30PM set to NORMAL=20
Hours</FONT></DIV>
<DIV><BR><FONT face=3DArial size=3D2>3:30PM - 6:30PM set to TIME &amp; =
HALF=20
Hours</FONT></DIV>
<DIV><BR><FONT face=3DArial size=3D2>Any time after 6:30PM - 7:29AM to =
be DOUBLE=20
TIME hours<BR><BR>Then if i set 4 other cells for data input:<BR>A2=3D( =
DATE 1)=20
29/5/2009 Start date.&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; =
B2=3D(Date 2)=20
29/5/2006 Finish date.<BR>A3=3D(Time) =
7:30AM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp; Start =
Time.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
B2=3D (Time) 7:00PM Finish Time.<BR><BR>How can i set 3 Cells say =
(C1:E1) to=20
display from the above example :<BR>C1=3D8 hrs (NORMAL =
TIME)<BR>D1=3D3hrs (Time=20
&amp; HALF)<BR>E1=3D2hrs (DOUBLE)<BR><BR>???</FONT></DIV></BODY></HTML>

------=_NextPart_000_0293_01C68406.3D280160--

0
5/30/2006 6:29:35 AM
excel 39879 articles. 2 followers. Follow

2 Replies
666 Views

Similar Articles

[PageSpeed] 23

If your time frames can not last more than 24 hours it becomes much more
straightforward. Hopefully this thread is a good starting point, as once
you have got times to shifts you just multiply them by the appropriate
amount!

http://www.excelforum.com/showthread.php?t=514668&highlight=shifts

Regards

Dav


-- 
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107
View this thread: http://www.excelforum.com/showthread.php?threadid=546611

0
5/30/2006 7:28:51 AM
This is a multi-part message in MIME format.

------=_NextPart_000_076D_01C683C4.01361CB0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Corey

Try
C1 =3DMIN(8,MOD(B3-A3,1)*24)
D1 =3DMIN(3,MAX(0,MOD(B3-A3,1)*24-C1))
E1 =3DMOD(B3-A3,1)*24-C1-D1

The MOD() part of the formula is there to deal with any situations where =
the end time is on a different day to the start time.
The *24 is to deal with the fact that Excel stores times as fractions of =
a day (24 hours)


--=20
Regards

Roger Govier


  "Corey" <coreymcconnell@splicetech.com.au> wrote in message =
news:OFsPsJ7gGHA.1324@TK2MSFTNGP04.phx.gbl...
  If i have 2 cells with time values say: A1=3D7:30AM and B1=3D3:30PM.

  7:30AM - 3:30PM set to NORMAL Hours

  3:30PM - 6:30PM set to TIME & HALF Hours

  Any time after 6:30PM - 7:29AM to be DOUBLE TIME hours

  Then if i set 4 other cells for data input:
  A2=3D( DATE 1) 29/5/2009 Start date.          B2=3D(Date 2) 29/5/2006 =
Finish date.
  A3=3D(Time) 7:30AM          Start Time.         B2=3D (Time) 7:00PM =
Finish Time.

  How can i set 3 Cells say (C1:E1) to display from the above example :
  C1=3D8 hrs (NORMAL TIME)
  D1=3D3hrs (Time & HALF)
  E1=3D2hrs (DOUBLE)

  ???
------=_NextPart_000_076D_01C683C4.01361CB0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2873" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>Hi Corey</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Try</FONT></DIV>
<DIV><FONT size=3D2>C1 =3DMIN(8,MOD(B3-A3,1)*24)</FONT></DIV>
<DIV><FONT size=3D2>D1 =3DMIN(3,MAX(0,MOD(B3-A3,1)*24-C1))</FONT></DIV>
<DIV><FONT size=3D2>E1 =3DMOD(B3-A3,1)*24-C1-D1</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>The MOD() part of the formula is there to deal with =
any=20
situations where the end time is on a different day to the start=20
time.</FONT></DIV>
<DIV><FONT size=3D2>The *24 is to deal with the fact that Excel stores =
times as=20
fractions of a day (24 hours)</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><BR>-- <BR>Regards</DIV>
<DIV>&nbsp;</DIV>
<DIV>Roger Govier</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV>"Corey" &lt;<A=20
  =
href=3D"mailto:coreymcconnell@splicetech.com.au">coreymcconnell@splicetec=
h.com.au</A>&gt;=20
  wrote in message <A=20
  =
href=3D"news:OFsPsJ7gGHA.1324@TK2MSFTNGP04.phx.gbl">news:OFsPsJ7gGHA.1324=
@TK2MSFTNGP04.phx.gbl</A>...</DIV>
  <DIV><FONT face=3DArial size=3D2>If i have 2 cells with time values =
say: A1=3D7:30AM=20
  and B1=3D3:30PM.</FONT></DIV>
  <DIV><BR><FONT face=3DArial size=3D2>7:30AM - 3:30PM set to NORMAL=20
  Hours</FONT></DIV>
  <DIV><BR><FONT face=3DArial size=3D2>3:30PM - 6:30PM set to TIME &amp; =
HALF=20
  Hours</FONT></DIV>
  <DIV><BR><FONT face=3DArial size=3D2>Any time after 6:30PM - 7:29AM to =
be DOUBLE=20
  TIME hours<BR><BR>Then if i set 4 other cells for data =
input:<BR>A2=3D( DATE 1)=20
  29/5/2009 Start date.&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; =
B2=3D(Date 2)=20
  29/5/2006 Finish date.<BR>A3=3D(Time) =
7:30AM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  &nbsp;&nbsp;&nbsp; Start =
Time.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  B2=3D (Time) 7:00PM Finish Time.<BR><BR>How can i set 3 Cells say =
(C1:E1) to=20
  display from the above example :<BR>C1=3D8 hrs (NORMAL =
TIME)<BR>D1=3D3hrs (Time=20
  &amp; HALF)<BR>E1=3D2hrs=20
(DOUBLE)<BR><BR>???</FONT></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_076D_01C683C4.01361CB0--

0
roger5293 (1125)
5/30/2006 7:35:28 AM
Reply:

Similar Artilces:

How do I get column headings to stay visible at all times in Exce.
I need the column headings to stay visible at all times on teh screen, and not just when you print the document? Hi! Select the ENTIRE row immediately under your column headers then goto Window>Freeze Panes. Biff "nemmettiv" <nemmettiv@discussions.microsoft.com> wrote in message news:323C229B-9E55-41C7-AB90-9021E1FBA8E4@microsoft.com... >I need the column headings to stay visible at all times on teh screen, and > not just when you print the document? Hi Select Cell A2. Windows->Freeze Panes to hold row 1 inplace or select B2. windows->Freeze Panes to ...

Extracting only time in CRM Reports
Hi there, I created Case-activities detail report using SRS. I need to get only the activity modified time(local time). I used the following expression on the textbox. iif (Fields!modifiedonutc.Value is nothing, "", Format(Fields!modifiedonutc.Value.ToLocalTime(), "t") and got the following error: Object variable or With block variable not set I should not get the above error because if the modifiedonutc is null, it should not change to localtime, right?? However, it is changing to localtime even the condition is not satified. Any idea would be greatly appreciate...

Record time peroid to run code
Hi Is there a msgbox method that will record the length of time it take for code to complete its execution. Thank you. Sub TestTime() Dim StartTime As Single StartTime = Timer 'do code here MsgBox "Code took " & Timer - StartTime & " seconds" End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gotroots" wrote: > Hi > > Is there a msgbox method that will record the length of time it take for > code to complete its execution. > > Thank you. The topic i...

date / time difference
in A1 previous date in A2 todays date in A3 i have formula.... =DATEDIF(A1,A2,"d") Not working, how do I get it to show the length of time between the 2 dates. Years and months I have cells formulated to dates Can anyone sort for me thanks What is "not working"? Error or ??? Works fine for me with real dates. Are the values valid dates? =DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, "& DATEDIF(A1,A2,"md") & " days" Gord Dibben MS Excel...

Emails sent to a distribution list are delivered multiple times
A user has sent an email (marketing info) to a distribution list (50+ addresses), the email is delivered to every address 50+ times. We have here Exchange 1003 with all the latest patches, emails are sent through a smart-host of our ISP. What can we do to prevent such things from happening? If I understand your question correctly then here it is ..... Under Exchange General Tab of your DL select the option "From Authenticated users only". WP. "Roland" wrote: > A user has sent an email (marketing info) to a distribution list (50+ > addresses), the email is ...

Business Portal Time & Expense Approval
Hi, It would be nice to be able to customize the 'Details' screens that a Time or Expense Approver sees? I have a customer that wants to be able to see Expense notes but there appears no way to do it as these pages are compiled. Thanks, Graham. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I...

Min Max buttons of Excel Worksheet
Hello, I recently recieved an Excel file from someone where the Min, Max & Close buttons of the Excel Worksheet are disabled. Also you cant move the Worksheet window around. How is that done ? How can they be enabled ? I know VBA coding & there are no Modules or code in the worksheet. Is this just a setting ? Any help would be greatly appreciated. Thank you, Jeff Hi Jrff The workbook is protected I think Tools>Protection.....Protect workbook (Select Windows) -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff" <Jeff@discussions.microsoft.com> wro...

Time Calculation
Can somebody help please. I am trying to enter this formula but something does not seem to work. Cell C2 containes a time data in the format HH:MM:SS i am trying to enter in cell D2 the following formula: =IF(C2=03:30:00,"ABC",(C2=04:30:00,"DEF",(C2=05:30:00,"GHI","FL"))) This does not seem to work with time, though it does work with general numbers. Thanks in advance =IF(C2=TIME(3,30,0),"ABC",(IF(C2=TIME(4,30,0),"DEF",(IF(C2=TIME(5,30,0),"GHI","FL"))))) If C2 is the result of a calculation, be aware that if...

Minutes to hrs calcultion
I have a form where you enter minutes but the calculation needs to give the result in Hr:min. I've tried A2=180 A3=20=SUM(A2,A3)/60 The result being 3.3 but I need it to show 3hrs 20 min Sounds daft I know! Mark You could try the following: =INT(A2/60)&" Hrs "& (MOD(A2,60))&" mins" this will look at A2 - if it has 200 the formula will translate that to 3 Hrs 20 mins edvwvw Mark Lewis wrote: >I have a form where you enter minutes but the calculation needs to give the >result in Hr:min. I've tried > A2=180 A3=20=SUM(A2,A3)/60 ...

Max and min
Hi! I would like a formula to anticipate the expected maximum and minimum of votes for each political group in the elections and that the variables are necessary to know Thanks in advance. an ...

PLEASE HELP: Display current Date & Time in Query Output
Hi All, How can I display current date and time in query output? Is there a way to do this? Thanks in advance You can use the built-in functions of Date() and Time() in the query. Today: Date() RightNow: Time() -- Duane Hookom Microsoft Access MVP "sam" wrote: > Hi All, > > How can I display current date and time in query output? > Is there a way to do this? > > Thanks in advance On Sun, 25 Apr 2010 20:39:01 -0700, sam <sam@discussions.microsoft.com> wrote: >Hi All, > >How can I display current date and time in...

Time Format Auto Entry AM and PM
The default time format is AM. I would like to create a format for cells that will only have PM entries. When someone writes 2:00, it will automatically upon exiting cell create 2:00 PM. I've looked in help and tried alterations of the General Time Format - No Luck. Is this a can do? BULAMAN Hi I think you'll need VBA. See: http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards Frank Kabel Frankfurt, Germany BulaMan wrote: > The default time format is AM. I would like to create a format for > cells that will only have PM entries. When someone writes 2:00, it > will ...

Abililty to Display Time portion of Date Time field
I'd like to be able to display Date and Time of certain timestamps on the web client screen. At the moment I can only show the date even though the database holds the Time aswell. This is very limiting as my users need to be able to see response times in hourly terms on Cases. This issue comes up again and again. We currently have cases that need a response within 24 hours or a penalty charge of $25 is made. Not being able to see the time is an absurd oversight. "Datapac" wrote: > I'd like to be able to display Date and Time of certain timestamps on the web >...

Turning One Time Gain Into Compound Annual Return
If you will receive 100 + X% of an investment in Y months, what is the best way to calculate the annual compound return represented by that? -- Will On Jun 10, 9:22 pm, "Will" <westes-...@noemail.nospam> wrote: > If you will receive 100 + X% of an investment in Y months, what is the best > way to calculate the annual compound return represented by that? Two equivalent ways are: =(1+x%)^(12/y) - 1 =(1 + rate(5,0,-1,1+x%))^12 - 1 PS: I assume you mean that you invested 100 and you will receive 100*(1+x%) in Y months. That is, your investment will grow by x% in Y ...

Run Time error When Outlook XP starts in WXP
I just upgraded my Dell PII 400 from W2000 SP4 to WXP SP1. I was having a lot of problems with Outlook XP (all updates applied) crashing multiple times while in use, which was 1 reason for the upgrade. Now when I start Outlook, most times I get a message box entitled: 'Microsoft Visual C++ Runtime Library' with the text: 'Runtime Error! Program: C:\PROGRA~1\MICROS~2\Office10\OUTLOOK.EXE abnormal program termination.' When I click OK, Outlook closes. The next time, it starts OK, but has to scan the Personal folder for errors. Fixes? Britt Dickson -- "In an era o...

Excel 2003
This is a bit like my last question. I want to fill a cell with either the minimum of two values, or, if that minimum is either zero or negative, then 0. I'm using =IF(MIN(F26,F28)>0,MIN(F26,F28),0) However, if cell f26 is empty, this formula will show the value of f28, rather than showing 0 as I had intended. Is there a version of this formula I can use that will automatically assume that blank cells = 0? Thanks - =IF(OR(F26="",F28=""),0,MAX(MIN(F26,F28),0)) HTH, Bernie MS Excel MVP "Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in...

When searching this type of folder, you can't search other folders at the same time
Hi, I have a user that is trying to use Advanced Find to search another users mail box for and e-mail in Outlook 2000 Sp3. When the user selects the browse button to select more than one directory to search, the following error message appears "When searching this type of folder, you can't search other folders at the same time". Also, if a top directory is selected, Advanced Find does not do a recursive search, but only searches the selected directory. The user has the owner role assigned to the folders in question, so I don't think that's it to do with permissions....

program loading time
The money 05 loads very slow, I have archived everthing past 90 days but it still loads slow. Any suggestions? In microsoft.public.money, oltexasboy wrote: >The money 05 loads very slow, I have archived everthing past 90 days but it >still loads slow. Any suggestions? Restore the archive as your main file, if you have not gone too far with it since the archive. Archiving did not help your speed appreciably, and you might want to refer to that older data. Consider reducing scheduled bills. Remove accounts from the budget if you think that would be appropriate. Set your start page t...

when recieving email the time is off how do i reset time
How do i reset my time on my microsoft outlook email the time is right on my computer but my email time is off On Mon, 5 Nov 2007 09:56:03 -0800, Lori Bucknell wrote: > How do i reset my time on my microsoft outlook email the time is right on my > computer but my email time is off You have posted this message to the wrong newsgroup. The access in this groups name refers to Microsoft Access, a database program. Please repost to the correct newsgroup for whatever program you are using. I would suggest you include your Windows and Office version number in the message. -- Fred Please r...

installing WLM second time
I had uninstalled my WLM because there were some issues with it (not all the e-mails were coming through) and then tried to install again - but got message that I have the copy of it already. I cannot find that copy anywhere on my computer and cannot download it either. Fortunately, I managed to install it on toolbar and that's how I can access it now - but how can I find it on my computer (advanced search turned the WML folder only) or add to my programs? Anyone knows what's going on? In Control Pane for Programs to uninstall it will be listed under Windows Live Essen...

Search for documents by time modified
I need to narrow a search for documents by author, a range of time (say from 5 p.m. to 8 p.m.), and a range of years (say from 1998 to 2004). They would need to include Word Perfect and Word documents. Thanks for any help. Version of Windows? Use Advanced Search. But if you're using Word2007 in Windows XP, you might not have much of a search function (it pretty much got moved from Office to Windows with the new versions). On Feb 19, 9:01=A0pm, D. Ring <D. Ring @discussions.microsoft.com> wrote: > I need to narrow a search for documents by author, a range ...

How Can I Know last time was activity on a database
I'm doing a process that uses databases from sys.databases. I would like to know when is the last time happened some activity there. (insert, update) I've tryed to look at sys.tables but only shows me last structural change on it. Not last time data has changed. How this can be done? Carles Oriol SQL Server doesn't store this information (imagine the overhead for all those who doesn't need it). You can for instance use a trigger to keep track of that information. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sq...

calculate time and date
I want to find out how many houer iy is between one date/time and another date/time. date and time one cell A1= 04.03.2005 08:30 date/time two cell A2= 05.03.2005 23:59 =A2-A1 custom format [hh:mm] or for Norway [tt:mm] -- Regards, Peo Sjoblom "make" <lage@tiscali.no> wrote in message news:2c61523.0503070721.2dacdd07@posting.google.com... > I want to find out how many houer iy is between one date/time and > another date/time. date and time one cell A1= 04.03.2005 08:30 > date/time two cell A2= 05.03.2005 23...

Time segments in OWA
I need to change the time segments in OWA when creating new appointments. The default is 30 minutes but I need 20 minutes. I can't seem to find the right code in the exchsrvr exchweb controls. I'm close but can't find the time array expression tha generates the 30 minute increments. Can anybody help me with this? Thanks, Brian Techhead <jorgenson.b@gmail.com> wrote: > I need to change the time segments in OWA when creating new > appointments. The default is 30 minutes but I need 20 minutes. I can't > seem to find the right code in the exchsrvr exchweb c...

How to Calulculate Hrs and Mins between time frames
This is a multi-part message in MIME format. ------=_NextPart_000_0293_01C68406.3D280160 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable If i have 2 cells with time values say: A1=3D7:30AM and B1=3D3:30PM. 7:30AM - 3:30PM set to NORMAL Hours 3:30PM - 6:30PM set to TIME & HALF Hours Any time after 6:30PM - 7:29AM to be DOUBLE TIME hours Then if i set 4 other cells for data input: A2=3D( DATE 1) 29/5/2009 Start date. B2=3D(Date 2) 29/5/2006 = Finish date. A3=3D(Time) 7:30AM Start Time. B2=3D (Time) 7:00PM =...