Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtracting times.
Excel 2000
Hi need some help on a time function please. Column A is formatted as time, h:mm, as users dont want to get involved with dates. Column B is formatted as number, one decimal place. A1 contains a start time,which could be anytime. User inputs a time of an event in A2, again in A3 etc. etc. over a period of 24 to 72 hours. I would like in B2, B3 etc.etc. the time period from the start time to the event. In column B i'm using the formula 24*(A2-$A$1) which works fine upto the first midnight when I then have to change the formula to 24*(A2-$A$1+1), then the next midnight I have to change it to +2, then +3. Can someone help me out with a correct function that doesn't need manual manipulation! please. TIA, derek |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtracting times.
In B2 put in the formula
=((A2-$A$1)+SUMPRODUCT(($A$2:A2<$A$1:A1)*1))*24 Then drag fill down the column. -- Regards, Tom Ogilvy derek wrote in message ... Excel 2000 Hi need some help on a time function please. Column A is formatted as time, h:mm, as users dont want to get involved with dates. Column B is formatted as number, one decimal place. A1 contains a start time,which could be anytime. User inputs a time of an event in A2, again in A3 etc. etc. over a period of 24 to 72 hours. I would like in B2, B3 etc.etc. the time period from the start time to the event. In column B i'm using the formula 24*(A2-$A$1) which works fine upto the first midnight when I then have to change the formula to 24*(A2-$A$1+1), then the next midnight I have to change it to +2, then +3. Can someone help me out with a correct function that doesn't need manual manipulation! please. TIA, derek |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtracting times.
Here is another way
in B2 =(A2-A1+(A2<A1))*24+B1 Then drag fill down the column. -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... In B2 put in the formula =((A2-$A$1)+SUMPRODUCT(($A$2:A2<$A$1:A1)*1))*24 Then drag fill down the column. -- Regards, Tom Ogilvy derek wrote in message ... Excel 2000 Hi need some help on a time function please. Column A is formatted as time, h:mm, as users dont want to get involved with dates. Column B is formatted as number, one decimal place. A1 contains a start time,which could be anytime. User inputs a time of an event in A2, again in A3 etc. etc. over a period of 24 to 72 hours. I would like in B2, B3 etc.etc. the time period from the start time to the event. In column B i'm using the formula 24*(A2-$A$1) which works fine upto the first midnight when I then have to change the formula to 24*(A2-$A$1+1), then the next midnight I have to change it to +2, then +3. Can someone help me out with a correct function that doesn't need manual manipulation! please. TIA, derek |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtracting times.
If you span days then you really need to design date in column A - the
users should understand that. For simplicity you could have them put number of days in an inserted column A, or 3 day DOW abbrev., or provide a listbox with the 7 weekdays. (But then could you span 7 days? It gets back to design.) Then you might as well use DATEDIF anyway, recognizing that there will be an error message if they enter a date earlier than the start. Failing that, if the span will never be more than 24 hours, just use IF comparing entered time to start time, reacting to negative results. None of these require programming. I suspect you don't really want a programming solution, but if so it's to use DateDiff. DateDiff will respect negative time spans, so you'll have to adjust appropriately via IF or IIF. On Sun, 24 Aug 2003 13:46:47 -0700, "derek" wrote: Excel 2000 Hi need some help on a time function please. Column A is formatted as time, h:mm, as users dont want to get involved with dates. Column B is formatted as number, one decimal place. A1 contains a start time,which could be anytime. User inputs a time of an event in A2, again in A3 etc. etc. over a period of 24 to 72 hours. I would like in B2, B3 etc.etc. the time period from the start time to the event. In column B i'm using the formula 24*(A2-$A$1) which works fine upto the first midnight when I then have to change the formula to 24*(A2-$A$1+1), then the next midnight I have to change it to +2, then +3. Can someone help me out with a correct function that doesn't need manual manipulation! please. TIA, derek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting Times | Excel Worksheet Functions | |||
Subtracting times | Excel Discussion (Misc queries) | |||
Subtracting Times | Excel Worksheet Functions | |||
Subtracting times | Excel Discussion (Misc queries) | |||
Subtracting Times | Excel Worksheet Functions |