Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple function made difficult
Bare with me on this one...I simply need to subtract A1 from A2 and have the
value appear in A3. All three cells are formatted as TIME. Here comes the complicated part. Before subtracting A1 from A2, I need to first calculate the values of A1 and A2 using the following functions within cell A3: MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1) MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1) Then all I need to do is subtract A1 from A2. Example A1 = 5:33 AM A2 = 3:09 PM without the MOD function A2-A1 = 9:36 With the MOD function A2-A1 = 9:45 How can I incorporate the MOD function in cell A3 which already contains =A2-A1 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple function made difficult
this, in a3, doesn't work?
=MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)-MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1) -- Gary "GreenMonster" wrote in message ... Bare with me on this one...I simply need to subtract A1 from A2 and have the value appear in A3. All three cells are formatted as TIME. Here comes the complicated part. Before subtracting A1 from A2, I need to first calculate the values of A1 and A2 using the following functions within cell A3: MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1) MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1) Then all I need to do is subtract A1 from A2. Example A1 = 5:33 AM A2 = 3:09 PM without the MOD function A2-A1 = 9:36 With the MOD function A2-A1 = 9:45 How can I incorporate the MOD function in cell A3 which already contains =A2-A1 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple function made difficult
One way:
=FLOOR(A2+7/1440,1/96) - FLOOR(A1+7/1440,1/96) or, with one less math operation =(FLOOR(A2*1440+7,15) - FLOOR(A1*1440+7,15))/1440 In article , "GreenMonster" wrote: Bare with me on this one...I simply need to subtract A1 from A2 and have the value appear in A3. All three cells are formatted as TIME. Here comes the complicated part. Before subtracting A1 from A2, I need to first calculate the values of A1 and A2 using the following functions within cell A3: MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1) MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1) Then all I need to do is subtract A1 from A2. Example A1 = 5:33 AM A2 = 3:09 PM without the MOD function A2-A1 = 9:36 With the MOD function A2-A1 = 9:45 How can I incorporate the MOD function in cell A3 which already contains =A2-A1 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I made a simple spreadsheet on Excel and it says its 34MB. How? | Excel Discussion (Misc queries) | |||
Simple Game and Puzzle - Very Difficult to Implement | Excel Worksheet Functions | |||
Simple Calculation - but Difficult! | Excel Discussion (Misc queries) | |||
Very simple, but difficult formula question | Excel Worksheet Functions | |||
URGENT.....Simple problem...made hard?? | Excel Programming |