Please help with time calculation

I posted this question before:
I need to calculate in a cell the time duration when the user inters start
and end times in the following fashion:

Start      End    Duration
00:00  00:45   0.75
00:45  01:30    0.75

Start and End times must be in 24hr format. My problem is in the duration. I
am required to calculate the duration like the example above i.e. instead of
showing it as 0:45 min (00:00 (12:00 AM) – 00:45 (12:45 AM) = 0:45 minutes,
my client requires it to show as three quarters of an hour in decimal format
(0.75). how can I do that? I need the user to enter 00:00 and 00:45 then the
form will calculate the 0.75.

and got the following solution:
(B2-B1)*24

The Problem:
this works fine with most but not in the following case:
Start     End
21:30    1:00
The answer I am getting is -20.5? it should be 3.5
any Idea.
Thanks
Al 
0
Al1 (451)
8/31/2005 11:47:04 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
487 Views

Similar Articles

[PageSpeed] 56

=(endTime - startTime)*24
format as general

Mangesh




"Al" <Al@discussions.microsoft.com> wrote in message
news:0201E013-4080-4719-A498-84B5EAAECAF9@microsoft.com...
> I posted this question before:
> I need to calculate in a cell the time duration when the user inters start
> and end times in the following fashion:
>
> Start      End    Duration
> 00:00  00:45   0.75
> 00:45  01:30    0.75
>
> Start and End times must be in 24hr format. My problem is in the duration.
I
> am required to calculate the duration like the example above i.e. instead
of
> showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
minutes,
> my client requires it to show as three quarters of an hour in decimal
format
> (0.75). how can I do that? I need the user to enter 00:00 and 00:45 then
the
> form will calculate the 0.75.
>
> and got the following solution:
> (B2-B1)*24
>
> The Problem:
> this works fine with most but not in the following case:
> Start     End
> 21:30    1:00
> The answer I am getting is -20.5? it should be 3.5
> any Idea.
> Thanks
> Al


0
8/31/2005 12:11:22 PM
Sorry, hadn't read the compete mail. Use:

=(B1-A1)*24+IF((B1-A1)*24<0,24,0)

B1 is end time
A1 start time

Mangesh



"Al" <Al@discussions.microsoft.com> wrote in message
news:0201E013-4080-4719-A498-84B5EAAECAF9@microsoft.com...
> I posted this question before:
> I need to calculate in a cell the time duration when the user inters start
> and end times in the following fashion:
>
> Start      End    Duration
> 00:00  00:45   0.75
> 00:45  01:30    0.75
>
> Start and End times must be in 24hr format. My problem is in the duration.
I
> am required to calculate the duration like the example above i.e. instead
of
> showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
minutes,
> my client requires it to show as three quarters of an hour in decimal
format
> (0.75). how can I do that? I need the user to enter 00:00 and 00:45 then
the
> form will calculate the 0.75.
>
> and got the following solution:
> (B2-B1)*24
>
> The Problem:
> this works fine with most but not in the following case:
> Start     End
> 21:30    1:00
> The answer I am getting is -20.5? it should be 3.5
> any Idea.
> Thanks
> Al


0
8/31/2005 12:13:33 PM
I used it and got the same answer "-20.5". did you try it?
I am subtracting 21:30 (start time) from 1:00 (End Time), it should be 3.5. 
This is the answer I am looking for. I have my cell formated as general.
thanks

"Mangesh Yadav" wrote:

> Sorry, hadn't read the compete mail. Use:
> 
> =(B1-A1)*24+IF((B1-A1)*24<0,24,0)
> 
> B1 is end time
> A1 start time
> 
> Mangesh
> 
> 
> 
> "Al" <Al@discussions.microsoft.com> wrote in message
> news:0201E013-4080-4719-A498-84B5EAAECAF9@microsoft.com...
> > I posted this question before:
> > I need to calculate in a cell the time duration when the user inters start
> > and end times in the following fashion:
> >
> > Start      End    Duration
> > 00:00  00:45   0.75
> > 00:45  01:30    0.75
> >
> > Start and End times must be in 24hr format. My problem is in the duration.
> I
> > am required to calculate the duration like the example above i.e. instead
> of
> > showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
> minutes,
> > my client requires it to show as three quarters of an hour in decimal
> format
> > (0.75). how can I do that? I need the user to enter 00:00 and 00:45 then
> the
> > form will calculate the 0.75.
> >
> > and got the following solution:
> > (B2-B1)*24
> >
> > The Problem:
> > this works fine with most but not in the following case:
> > Start     End
> > 21:30    1:00
> > The answer I am getting is -20.5? it should be 3.5
> > any Idea.
> > Thanks
> > Al
> 
> 
> 
0
Al1 (451)
8/31/2005 12:33:06 PM
I am getting 3.5
Please check the values B1 is end time and A1 start time.

Mangesh



"Al" <Al@discussions.microsoft.com> wrote in message
news:FCFB1214-095C-4EAA-B1F5-FC6D57C0BCE6@microsoft.com...
> I used it and got the same answer "-20.5". did you try it?
> I am subtracting 21:30 (start time) from 1:00 (End Time), it should be
3.5.
> This is the answer I am looking for. I have my cell formated as general.
> thanks
>
> "Mangesh Yadav" wrote:
>
> > Sorry, hadn't read the compete mail. Use:
> >
> > =(B1-A1)*24+IF((B1-A1)*24<0,24,0)
> >
> > B1 is end time
> > A1 start time
> >
> > Mangesh
> >
> >
> >
> > "Al" <Al@discussions.microsoft.com> wrote in message
> > news:0201E013-4080-4719-A498-84B5EAAECAF9@microsoft.com...
> > > I posted this question before:
> > > I need to calculate in a cell the time duration when the user inters
start
> > > and end times in the following fashion:
> > >
> > > Start      End    Duration
> > > 00:00  00:45   0.75
> > > 00:45  01:30    0.75
> > >
> > > Start and End times must be in 24hr format. My problem is in the
duration.
> > I
> > > am required to calculate the duration like the example above i.e.
instead
> > of
> > > showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
> > minutes,
> > > my client requires it to show as three quarters of an hour in decimal
> > format
> > > (0.75). how can I do that? I need the user to enter 00:00 and 00:45
then
> > the
> > > form will calculate the 0.75.
> > >
> > > and got the following solution:
> > > (B2-B1)*24
> > >
> > > The Problem:
> > > this works fine with most but not in the following case:
> > > Start     End
> > > 21:30    1:00
> > > The answer I am getting is -20.5? it should be 3.5
> > > any Idea.
> > > Thanks
> > > Al
> >
> >
> >


0
8/31/2005 12:50:14 PM
Sorry, It works. thank you very much, you have been a great help.
Al

"Mangesh Yadav" wrote:

> I am getting 3.5
> Please check the values B1 is end time and A1 start time.
> 
> Mangesh
> 
> 
> 
> "Al" <Al@discussions.microsoft.com> wrote in message
> news:FCFB1214-095C-4EAA-B1F5-FC6D57C0BCE6@microsoft.com...
> > I used it and got the same answer "-20.5". did you try it?
> > I am subtracting 21:30 (start time) from 1:00 (End Time), it should be
> 3.5.
> > This is the answer I am looking for. I have my cell formated as general.
> > thanks
> >
> > "Mangesh Yadav" wrote:
> >
> > > Sorry, hadn't read the compete mail. Use:
> > >
> > > =(B1-A1)*24+IF((B1-A1)*24<0,24,0)
> > >
> > > B1 is end time
> > > A1 start time
> > >
> > > Mangesh
> > >
> > >
> > >
> > > "Al" <Al@discussions.microsoft.com> wrote in message
> > > news:0201E013-4080-4719-A498-84B5EAAECAF9@microsoft.com...
> > > > I posted this question before:
> > > > I need to calculate in a cell the time duration when the user inters
> start
> > > > and end times in the following fashion:
> > > >
> > > > Start      End    Duration
> > > > 00:00  00:45   0.75
> > > > 00:45  01:30    0.75
> > > >
> > > > Start and End times must be in 24hr format. My problem is in the
> duration.
> > > I
> > > > am required to calculate the duration like the example above i.e.
> instead
> > > of
> > > > showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
> > > minutes,
> > > > my client requires it to show as three quarters of an hour in decimal
> > > format
> > > > (0.75). how can I do that? I need the user to enter 00:00 and 00:45
> then
> > > the
> > > > form will calculate the 0.75.
> > > >
> > > > and got the following solution:
> > > > (B2-B1)*24
> > > >
> > > > The Problem:
> > > > this works fine with most but not in the following case:
> > > > Start     End
> > > > 21:30    1:00
> > > > The answer I am getting is -20.5? it should be 3.5
> > > > any Idea.
> > > > Thanks
> > > > Al
> > >
> > >
> > >
> 
> 
> 
0
Al1 (451)
8/31/2005 12:50:46 PM
Another option would be to include both the date and time in the cell.  This
would make it a little more robust--if you crossed two midnights.

Al wrote:
> 
> I posted this question before:
> I need to calculate in a cell the time duration when the user inters start
> and end times in the following fashion:
> 
> Start      End    Duration
> 00:00  00:45   0.75
> 00:45  01:30    0.75
> 
> Start and End times must be in 24hr format. My problem is in the duration. I
> am required to calculate the duration like the example above i.e. instead of
> showing it as 0:45 min (00:00 (12:00 AM) – 00:45 (12:45 AM) = 0:45 minutes,
> my client requires it to show as three quarters of an hour in decimal format
> (0.75). how can I do that? I need the user to enter 00:00 and 00:45 then the
> form will calculate the 0.75.
> 
> and got the following solution:
> (B2-B1)*24
> 
> The Problem:
> this works fine with most but not in the following case:
> Start     End
> 21:30    1:00
> The answer I am getting is -20.5? it should be 3.5
> any Idea.
> Thanks
> Al

-- 

Dave Peterson
0
petersod (12004)
8/31/2005 12:57:02 PM
slightly shorter option

>=(B1-A1)*24+IF((B1-A1)*24<0,24,0)

=(B1-A1+(A1>B1))*24

hth RES
0
8/31/2005 1:40:37 PM
Reply:

Similar Artilces:

interesting question can anyone help
this may not be the right place to ask this but i have an xda 2s pda from O2, is it possible to make it send and automated text message to my contacts at 12pm the day before they are due to meet me for an appointment eg client joe bloggs (contact details are in contacts in the pda synced with outlook) appointment tomorrow at 4 pm automated text reads "joe bloggs your appointment tomorrow is at 4 pm thank you" and so on for all other appointments that day? as i say it may not be the best place to ask the question and i dont even know if its possible any help would be apporechiat...

Need help with macros and protections
I want to protect a sertain range of titles and leave the remainin worksheet open for data entry. Everytime I try to do this protection i requires the entire sheet to be protected and a password to b activated for the effected range. I want the unprotected range withou a password entry. Secondly I have created two buttons to list data in assending an decending order. I got them working but now they give me erro messages. Also the assending macro places all 0 enteries on the top need the macro to read greater that 0 Any suggestions would be appreciated. Macros: Sub Macro6() ' ' Macr...

Using Time in formulas #2
Frank: I don't want the user to enter them by hand. I want to use a formul to calculate the subsequent times. They would have to load 9 different lines. Staci -- SPenne ----------------------------------------------------------------------- SPenney's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=107 View this thread: http://www.excelforum.com/showthread.php?threadid=26920 ...

Help With RSS Red Feed Buttons
Hi All, Thanks for letting me join. I have a question. I just downloaded a free version of an rss software for reading, writing and publishing; I have about 12 days left on it. Had good customer service when I downloaded it, they even called me and walked me through using it. I published two articles for my two websites..... But........the weekend came and i discovered that the little red rss buttons were not up on my site. I emailed them and they sent me a few red rss buttons and said that I needed to put them on my sites (I have two sites) and then link it back to the feed. I don't h...

Help needed on date matching and cell reference.
Hi, I need a formula to return the value of a cell from a nearby column, based on a matching of the months and years within a range of dates to the months and years within a given date. Here is the setup: Cell BA58 contains the given date. Column AP, starting in Cell AP59 and going down to AP2000 contains the array of dates that need to be evaluated to find which date matches the given date in Cell BA58 Column AR, starting in Cell AR59 and going down to AR2000 contains the array of numbers from which the result must be displayed. If a match is found between the given date in...

I need help with multiple Fidelity accounts and Money 2006
Here is my situation: I have the following accounts at Fidelity: Under my login ID: Financial Institution is named "Fidelity Investments" for these accounts My Rollover IRA My Roth IRA Joint brokerage account Under my wife's login ID: Financial Institution is named "Fidelity Investments # 2" for these accounts Wife's Rollover IRA Wife's Roth IRA Joint brokerage account The Joint Brokerage account is the same, and shows up at the Fidelity web site when either of us logs into Fidelity. This setup is giving m...

Help to import data from reference workbook
I wasn't sure if this would require VB or not so I've put this in the General Question group. I have 2 workbooks where the first 3 columns of each row (after a title row) consist of an ID number, a North coordinate and an East Coordinate. Workbook A is complete in that every ID for our system is included along with the coresponding North and East coords. Workbook B includes a subset of the IDs and only some of the IDs have their coords in columns B and C. How do I import the coords into Workbook B without having to copy/paste a thousand or more times. Thanks in advance for a...

Activating bold function takes time
I have a PC which is having Office XP installed in it. When i try to make a text in a cell bold the first time in any worksheet, it takes aprroximately 4-5 mins, but the very next moment if i want to make another cell bold, it happens as normal. can u help with the reason why it so happens & is there any solution for the same. I also tried reinstalling Office (different version) twice after removing the previous installed version but the problem is still persisting. I think I've read posts that say that when this kind of thing happens, it's usually because the current printe...

receiving messages multiple times
Outlook 2002, set in POP3 mode, set to leave a copy of messages on the (offsite) server. Whenever mail is received at the server, it is forwarded properly to this user, but then in 5 minutes or so when Outlook checks the server again (as it has always done in the past) another copy of the message is sent. This will continue until a NEW message arrives at the server, at which point that new message will be repeatedly sent. There is only one copy of the email on the server, and another user who monitors this account using Outlook Express properly receives a single instance of the messa...

LOOKUP Help #7
Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot One way using non-array formulas is illustrated in this sample construct: http://www.savefile.com/files/720924 Placing source dates under correct month col....

restoring an individual mailbox to a give point in time
Have any of you needed to do this and succeeded? What steps did you take? not an individual mailbox - only blb will allow you to restore a single mailbox, but that cannot be done to a point in time; only from when the backup was taken...only way to do this is to restore the entire database, then replay all applicable transaction log files... "dondidati" <armtrece@vba.va.gov> wrote in message news:5cad01c48143$bfd1dbd0$a401280a@phx.gbl... > Have any of you needed to do this and succeeded? What > steps did you take? ...

Can i replay logs with eseutil multiple times on the same EDB file?
hi all, I was wondering if anyone was ever able to replay logs through eseutil multiple times on the same database. I tried issueing the command "eseutil /r E00 /D" once on the original set of logs and it succeeded. When I added some more logs to the same folder and issued the same command, it appears that no new logs are replayed to the database. However, if I only call ESEUTIL once on the comprehensive set of logs (the original set of logs plus the additional ones), then it works fine and all the logs are incorporated. It seems that ESEUTIL will only allow you to replay logs to ...

Formulas not calculating???
My excel is no longer calculating formulas, when referencing other cells. How can I correct this? Hi maybe: Tools - Options - Calculate and enable automatic calculation -- Regards Frank Kabel Frankfurt, Germany "J Dizzle Fizzle" <J Dizzle Fizzle@discussions.microsoft.com> schrieb im Newsbeitrag news:9DD3E243-E887-458D-B6A4-C389652C2B27@microsoft.com... > My excel is no longer calculating formulas, when referencing other cells. > How can I correct this? ...

Excel:I set the font color to be "Red". Next time file is open, f.
Say I sent the font color to Red in the cell. The next time I open the file, the font color in the cell changes? Why Maybe you didn't save the file? >-----Original Message----- >Say I sent the font color to Red in the cell. The next time I open the file, >the font color in the cell changes? Why >. > ...

Please verify BUG in Microsoft Windows XP/2003 Accessibility *MPS*
Hello, The Problem (Multi Processor only): This occurs when there is MORE than one Logical/Physical CPU on the system. When a console winevent hook function is active, changing the codepage of a console window causes the console window to not respond. I set a hook using SetWinEventHook to get notified on events EVENT_CONSOLE_CARET to EVENT_CONSOLE_END_APPLICATION all is well, events are coming. When I open a CMD window and change the codepage to a different codepage than the default one, the window stops responding. From my tests it appears that the problem is somewhere in the SetCo...

Convert number in time #2
Thank you! : -- Megadat ----------------------------------------------------------------------- Megadata's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1487 View this thread: http://www.excelforum.com/showthread.php?threadid=26506 ...

Automatic email entry help
when i send a new email in outlook 2003, if i type the first letter of the name in the to blank, it brings up a list of email addresses. can i edit these addresses??? Those email addresses are pulled from a cache of previously typed email addresses - what do you need to edit? Is one of them wrong? If yes, just use the arrow key to select it and press delete. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Keith Girdley aske...

calculations between days
Hello everybody I have a problem of customer service report and i need to solve a problem but i do not know if there is such a formula. I have two dates. One is the requested day 23/01/2004 (E50), and the second one is the Delivery day 10/05/2004 (Z5). I want to express the difference between the two days, but i want to see the result as a week number. There is any possibility to change the days to week number? Also in Column E i have the requested days (day format) In the row 5 from column k to bb i have the days of delivery (days format). In the area table i have the orders. the...

Need help with recursive SQL
Hi, I am using MS Access / Jet. Problem is of hierarchial query: I mean typical Boss Employee table: Name Boss ===== ===== John NULL Sam John Michael Sam William Sam Alfred William I need a query that if I take John then all names should be returned in a single coulmn because he is the highest boss, if i take sam, then michael, william and alfred should be returned. If I take Michael then only Michael should come since no one is working under him. Ok, one boss can have many emplo...

IF statement help needed
I am using excel 2007. I have a spreadsheet that contains data: Acct no Date PPT Paid Sales Tax Amount 1000 4/30/10 60.46 4.54 65.00 1000 4/30/10 32.55 2.45 35.00 1000 4/30/10 65.00 0 65.00 1000 4/30/10 35.00 0 35.00 (PPT Paid + Sales Tax = Amount). I want to exclude the last 2 records. I can't use sales tax > 0 because I have other records that may have no sales tax but are still valid. I have tried the delete duplicates on the menu bar and it helps...

Help
email dropped - store will not mount (exchange 2003 on windows 2003 server) here are the different errors messages I see: The Exchange store 'First Storage Group\Mailbox Store ( is limited to 18 GB. The current physical size of this database (the .edb file and the .stm file) is 12 GB. If the physical size of this database minus its logical free space exceeds the limit of 18 GB, the database will be dismounted on a regular basis. Event Type: Warning Event Source: MSExchangeIS Event Category: General Event ID: 1240 Date: 3/2/2007 Time: 3:27:24 PM User: N/A Computer: myserver Descr...

Re: Count "hours", as 1 each? Example attached....Please Help!
I am totally stumped... I would like if possible to count how many hours each employee worked, from a pivot table. I have already captured how many scans each employee did, just need to get a count of total number of hours each worker worked. ( Productivity report ) Example: a b c d e f g H 1 employee 8 AM 9AM 10AM 11AM 12PM 1PM TOTAL 2 3 BOB 40 49 50 55 80 274 4 NANCY 50 60 50 100 30 290 5 SUE 25 20 20 20 85 blank cells are when the worker scanned ...

some one help with the function (OR)
some one help with the function (OR) I dont know how to use this function thankyou for al -- Message posted from http://www.ExcelForum.com Did you check the help file? What part of the explanation didn't you understand? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <nader.198htq@excelforum-nospam.com>, nader <<nader.198htq@excelforum-nospam.com>> says... > some one help with the function (OR) > I dont know how to use this function > > thankyou for all...

Formula for a fill color help
I can't figure out how to make a formula that will recognize a color a a value. More specifically; If b1 is made to be green (color index 4) would like c1 to insert the letter Y. I am I'm looking for an I statement so I can use it throughout the whole workbook. The formula if I understand it should kind of look like (in C1): =If(b1=colorindex4,"Y","") I'm looking to put a "Y" in c1 if b1 is colored in green. I would really like to also know how to insert a fill color in an formula! Thank you for any of your help and time, Brya -- Bryan J Yo...

Pop Up Calendar Help
I am very new to Excel, working with 2003. I have followed the directions her http://www.fontstuff.com/vba/vbatut07.ht I think I am very close, but when I try to test the code I get run time erro Method'Value' of object 'Icalendar' failed When I select the debug it takes me t the line frm.Calendar.Sho Any help is greatly apprectiead Andrea Andrea, Did you rename the userform to frmCalendar? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Andrea" <anonymous@discussions.mi...