Trouble with "time" in formulas

I have a time sheet that looks something like this:

A1=time...A2=time...A3=A2-A1+if(A1/24,1)...A4=if(or(A3<time(0,15,0),A3>time(4,0,0)),A3,time(4,0,0))

A1 and A2 are set up as dropdown lists and the times in the list are i
increments of 15 minutes. WHat I'm trying to accomplish is (in cell A4
if A3 is between 15 minutes and 4 hours, then return 4 hours. if A3 i
0 or greater than 4 hours, return A3.

If I enter enter times in A1 around 00:00 and A2 a little after 00:0
it works fine... example:

A1=22:00...A2=01:00...A3=03:00...A4=04:00
A1=20:30...A2=00:15...A3=03:45...A4=04:00
A1=00:00...A2=00:00...A3=00:00...A4=00:00

If I enter times after 00:00 in either A1 or A2 it doesn't work..
example:

A1=01:00...A2=03:30...A3=02:30...A4=02:30
A1=13:00...A2=14:15...A3=01:15...A4=01:15

Can anyone help

--
Message posted from http://www.ExcelForum.com

0
5/18/2004 9:37:55 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
686 Views

Similar Articles

[PageSpeed] 49

Hi
try
=IF(A3=0,0,MAX(A3,4/24))
and format this cell as time

--
Regards
Frank Kabel
Frankfurt, Germany


> I have a time sheet that looks something like this:
>
>
A1=time...A2=time...A3=A2-A1+if(A1/24,1)...A4=if(or(A3<time(0,15,0),A3>
time(4,0,0)),A3,time(4,0,0))
>
> A1 and A2 are set up as dropdown lists and the times in the list are
> in increments of 15 minutes. WHat I'm trying to accomplish is (in
> cell A4) if A3 is between 15 minutes and 4 hours, then return 4
> hours. if A3 is 0 or greater than 4 hours, return A3.
>
> If I enter enter times in A1 around 00:00 and A2 a little after 00:00
> it works fine... example:
>
> A1=22:00...A2=01:00...A3=03:00...A4=04:00
> A1=20:30...A2=00:15...A3=03:45...A4=04:00
> A1=00:00...A2=00:00...A3=00:00...A4=00:00
>
> If I enter times after 00:00 in either A1 or A2 it doesn't work...
> example:
>
> A1=01:00...A2=03:30...A3=02:30...A4=02:30
> A1=13:00...A2=14:15...A3=01:15...A4=01:15
>
> Can anyone help?
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
5/18/2004 10:07:19 PM
Reply:

Similar Artilces:

Business Portal Project Time & Expense Email Notifications
Hi, Utilising an online system such as the Business Portal Project Time and Expense to facilitate the entry of employee timesheets and expense reports would be made even simpler by having the system cater for email notifications (such as in Requisition Management and EDD). Why is this important? Well, with setting up the system to cater for the types of users who will not be office bound, but do have access to their email while roaming, notfication of saves, submissions, rejections, re-submissions and approvals would greatly facilitate their ability to manage timesheets and expense re...

Time of Day in email is wrong
One XP computer - using Outlook for email. Receive an email at 9:54am Reply at 9:55am Outlook reports the received email was received at 10:54am Time zone on computer is correct. Outlook reports that reply was sent at 10:55am What could be causing this? It would be good to get it fixed. Thanks, Fred Check your daylight savings time settings under both Control Panel and Outlook Calendar options. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be d...

How do I set up an excel spreadsheet to update every time a user .
For some reason I cannot remember how to do this... I want my spreadsheet to update (retain the changes that other users make) automatically. How do I do this? Help! JM Jennie, are you talking about saving the workbook when it is closed? if so put this in the thisworkbook code, if the user enables macros the file will save all changes before it closes Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Save End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answer...

Budget Time period change
In the budget section of MS Money it averages the budget from the past year automatically. Is there a way to change the time period to 3 months, or 6 months? When I installed MS Money several months ago I imported data from the March to April time frame, but not enough accurate data to go back one year, the time from used in budget estimates. So... Any way to change this? I've looked and can not find an adjustment for this setting. Thanks! Mike Mike Alexander PP-ASEL Temecula, CA See my online aerial photo album at http://flying.4alexanders.com Hello? Anyone? Mike 'Flyin&#...

Printing a workbook one row at a time filling in a form with the d
Hello I have a workbook with about 20,000 rows of data. I want to design a form and have the information from one row inserted into the form and be printed out. The form would also have empty places for the sales people to write additional information on the paper after they make the sales call. The information is name, phone number, address and several other customer related information cells. I am using Excell 2000. IF possible I would like to print these 2 up on a standard piece of paper 8.5 by 11 inches. This means I would print two forms on one page with a seperate record/row fi...

date time format
i wish to have dates and time in a column in such a way that after 24 hours day changes and then again for next day i have 24 hours and so on. plz tell how could i do this using excel. In address A1 type in =today()+"00:00" In the A2 type in =A1+1/24 Drag this formula down Regards -- Bonzo123 ------------------------------------------------------------------------ Bonzo123's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24182 View this thread: http://www.excelforum.com/showthread.php?threadid=465824 thanks a lot for ur response , however m...

Trouble with "time" in formulas
I have a time sheet that looks something like this: A1=time...A2=time...A3=A2-A1+if(A1/24,1)...A4=if(or(A3<time(0,15,0),A3>time(4,0,0)),A3,time(4,0,0)) A1 and A2 are set up as dropdown lists and the times in the list are i increments of 15 minutes. WHat I'm trying to accomplish is (in cell A4 if A3 is between 15 minutes and 4 hours, then return 4 hours. if A3 i 0 or greater than 4 hours, return A3. If I enter enter times in A1 around 00:00 and A2 a little after 00:0 it works fine... example: A1=22:00...A2=01:00...A3=03:00...A4=04:00 A1=20:30...A2=00:15...A3=03:45...A4=04:00 A1=00...

Timed macro
How can I get a specific macro I have created to do it's function every 1 minute automatically? Any help would be appreciated. Chip Pearson has lots of instructions at: http://www.cpearson.com/excel/ontime.htm ftahbaz@gmail.com wrote: > > How can I get a specific macro I have created to do it's function every > 1 minute automatically? > > Any help would be appreciated. -- Dave Peterson Didnt really help...too confusing for me. Dave Peterson wrote: > Chip Pearson has lots of instructions at: > http://www.cpearson.com/excel/ontime.htm > > ftahbaz@gm...

How do you include a custom field in a time scaled data report
Hi There, Does anyone know how to include a custom field in a time scaled data report? I created a custom field, (text 8 = Projects) to organize my tasks by projects, similar to organizing task by dept.. I would like to see actual work organized by the projects. Is there a way I need to organize my project plan for me to collect this data? Thank you, Wina Hi Wina, If I understand your question corerrectly, you wish to have a time phases custom field simular to work or cost? Unfortunalty Custom fields are not "assignment" fields and therefore cannot be m...

Date AND Time
Why do some views that contain a column for "Created On" have the Date and Time and some only have a Date? I'd find it really handy to add the date AND time of "Created On" to the notes page (I know this is unsupported)... Thanks all Tom ...

Allow lag time or travel time for Meetings / Appointments
Many times when an organizer sets up a meeting, they set the begin and end time of the actual meeting. At times, a meeting is organized at a location where some attendees need to travel. During travel time, they will clearly be out of office. I suggest creating a feature so that attendees could enter estimated travel time within the same meeting event so that their status will be Out-of-Office before, during and after the meeting. Another issue with setting an accurate begin and end meeting time is that sometimes meetings will finish earlier / later than estimated. In other ...

Date-time axis in chart
Hi, In my data sheet the first column is date-time and then a second column for the graph. 07/01/01 10 07/04/05 15 08/01/01 12 The format for first column is: Date then I selected 2001-03-14 (predefined) as Type. When I draw a chart, the hor. axis does not show like a date-time axis. With format-axis I also selected the same date-time format as in the sheet. And linked to the source. I see the correct date-time labels along the axis, but if I change a date-time in the sheet, the label on the axis also changes but not the position on the horizontal axis. Any idea what might be ...

Windows Media Player 12 (Video Run Time)
Can the video length be displayed in the player while it's running? Thanks WMP automatically hides the length if the width of the Now Playing window is too small. Make the window bigger until the length is displayed. If WMP still only displays the current position, click the time display until it shows both the current position and the total length. Regards -- Tim De Baets http://www.bm-productions.tk On 31/05/2010 17:25, Skylo wrote: > Can the video length be displayed in the player while it's running? Thanks ...

Time Based Calculations
-- Sanjay Jain Gurgaon Haryana India Try Chip's page at: http://www.cpearson.com/excel/datetime.htm#AddingTimes -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "jain_sanju" <jain_sanju@hotmail.com> wrote in message news:CE322259-89C9-4D71-865D-A07B24F0DDF2@microsoft.com... Re: Time Based Calculations > > -- > Sanjay Jain > Gurgaon > Haryana > India That's not a whole lot to go on but if the times are entered as XL times then mulitply the times by 24 to turn them into numbers that can be used in calc...

Rounding Up Time
How do I round up a time in increments of 30 mins? Example: 0.75 Hr = 1.0 hour, 1.15 = 1.50 hour , 5.10 hour= 5.50 Renee, Try using the Round function. Something similar to (Round[number1]) John "Renee" wrote: > How do I round up a time in increments of 30 mins? > Example: 0.75 Hr = 1.0 hour, 1.15 = 1.50 hour , 5.10 hour= 5.50 Try something like: Round(([your value]+.249999) * 2,0)/2 -- Duane Hookom Microsoft Access MVP "Renee" wrote: > How do I round up a time in increments of 30 mins? > Example: 0.75 Hr = 1.0 hour, 1.15 ...

Time Problem
I have uploaded an ASCII text file to Excel. One of the fields is a 6 digit field for time. When I get it in the spreadsheet, I can not change it to time (154503 - should be 15:45:03). I've tried all the time and custom features but it still won't work or it will give me 00:00:00. Any ideas what I need to do? -- --------------------------------------------- Robert (RJ) Smith T.E.M. Beaumont Police - Sgt. Beaumont Emergency Management - Assist Coord. P.O. Box 3827 Beaumont Tx 77701 409-880-3830 (Off) 409-880-3895 (Fax) www.disaster-research.us "Robert Smith" <sgtsmit...

Windows Installer runs each time I download an email
I am running WinXP Pro SP1 with MSOffice 2002. I recently upgraded to Outlook 2003, now when I download a new e-mail Windows Installer runs! If I leave it alone it usually finishes and allows me to read the mail, but it runs again if I have to open an attachment. Can someone please tell me what the heck is going on here and how I can fix it? Thanks! Hi Aejen, I too have the same problem, but so far no one has come forward with a solution. I am running Win XP Home Edition with upgraded Office 2003. I have uninstalled it and reinstalled it. Still the same problem. Its not a major probl...

how do i enter date and time in cell without it changing
I WANT TO USE COMPUTER DATE AND TIME IN A CELL IN A WORKSHEET BUT I DO NOT WANT IT TO CHANGE AFTER A MINUTE I WANT IT TO STAY AS IT WAS FIRST ENTERED IS THIS POSSIBLE. iS THERE ANYWAY TO HAVE THIS AS A FORMULAE ? "Paul B" wrote: > bete, Ctrl+; for date and Ctrl+shift+; for time > > -- > Paul B > Always backup your data before trying something new > Please post any response to the newsgroups so others can benefit from it > Feedback on answers is always appreciated! > Using Excel 2002 & 2003 > > "bete" <bete@discussions.microsoft.c...

We are trying again, Time problem
1st thanks for the guys that helped already, we learned a bunch from them. We worked on this sheet for weeks, 1 problem left (I hope) The problem is the time in cell a1 12:58 pm and the next time in cell a2 1:15 am. 1:15am is ahead of the time of 12:58pm but excel says nope. Even in number format of 1258 & 115 the 115 is a lower time. Our format is a conditional format =a2<a1+11 < in number format, we did this with the time format =a2<a1+"00:11" We did several different ways and Excel see's 1:15am as less then the above time of 12:58pm but it's really ahea...

NDR won't time out and keeps killing Outlook
Hi, I have a user who's Outlook has been crashing periodically for about a week. We think we've traced it back to an NDR he has been getting repeatedly for a week from the System Administrator, and I've been able to duplicate the crash on another system. The NDR appears to be in response to a recall request he made on a message to an outside company (not realizing this wouldn't work I guess). Each time the NDR comes into his mailbox, his Outlook shuts down. He doesn't even have to highlight it. There is nothing in the app or sys log on either the client or the ser...

12.2.3 Updater errors every time.
Auto Updater reminds me every time I run an Office app that I need to update to the CRITICAL 12.2.3 update of Office 2008 (12.2.1) on my Intel Power Book running up-to-date OS 10.6.2. EVERY time it tries to download it again, EVERY time it starts the install, then EVERY time it errors out saying : "The Installation Failed. The Installer encountered an error that caused the installation to fail. Contact the software manufacturer for assistance." It goes through the progress bar saying Writing files, then switches to Running Package Script - at which point the progress b...

Adding Subtracting Time
Hello, I need to make a time sheet work sheet. My problem is I want to Subtract time to a number value... I.E. 12:00PM TO 8:00PM = 8 how would i do this? Since XL stores times as fractional days (e.g., 06:00 = 6/24 = 0.25), you can multiply by 24 =(B1-A1)*24 Note however, that if the times span midnight, the "later" time will be less than the earlier time. In that case you need to add 24 hours (=1). You can use XL's implicit coerciion of TRUE/FALSE to 1/0: =(B1-A1+(B1<A1))*24 or you can use XL's MOD() function: =MOD(B1-A1,1)*24 For more on time...

Peak time analysis
If you leave your email address on this thread I will email you a peak time analysis template for the 24 hour clock and across 7 days. Very useful for peak time analysis both strategic and tactical analysis. >Peak time analysis allows the analyst to look at a group of events and determine from the information available what is the most likely time / day for those crimes to have happened. >The template is intended to tackle the issue of between times, the earliest and latest time a event (ie burglary) could have occurred (rarely do we know exactly when a crime occurred). >This enab...

how do I review two ppts at the same time
Running office small business 2007, want to review two ppts one one screen at the same time. I have done this with office 2003. Thank you in advance for your help. JA Jim, Open PP, minimize and drag out to fill both monitors, then open your two files and select View>Cascade. Both will open in the PP real estate and then you can manually size, one on each monitor. "Jim" wrote: > Running office small business 2007, want to review two ppts one one screen at > the same time. I have done this with office 2003. > Thank you in advance for your help. >...

Plotting Irregular Dates and Times
I have data that is formatted mm/dd/yyyy hh:mm with associated Y-axis values. I want the y axis data to be spaced properly on the x axis. With the auto format or Date formated x-axis the smallest unit is a day; is there a way to space out the hours and minutes as well? For example, Date and Time Duration 8/17/2007 10:04 40 seconds 8/18/2007 12:27 25 seconds 8/18/2007 15:35 40 seconds 8/19/2007 13:14 60 seconds With the settings I am using now, I only get 1 column for the 18th, but it should have two. Any suggestions, or am I at the limitations of the software? Try an XY cha...