[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
third
Newbie

Joined: 27 Sep 2011
Posts: 1

 Posted: Tue Sep 27, 2011 4:53 am    Post subject: [Solved] Formula to check state between two timestamps Hi! I'm trying to create a formula which will check is the event is in progress or not but not enough skills to to do so I kindly ask for help. Example: Cell A1 - start date i.e. 2011-03-10 10:43 Cell B1 - end date i.e. 2011-03-11 14:20 I need formula which will check is the event started is still in progress at 2011-03-10 18:00. I have hundreds of rows to check like this so I wouldn't like to split the timestamps between date and time. Can someone help me?Last edited by third on Wed Sep 28, 2011 4:53 am; edited 1 time in total
scsisys

Joined: 17 Dec 2009
Posts: 248

Posted: Tue Sep 27, 2011 11:14 am    Post subject:

Two options I can present would be:

First, in column E, enter the start date as in A1 and the time stamp 18:00.

Then, Enter the formula below in D1.

=if(b1-a1>=e1-a1;"active";"ended")

If D1 is not already highlighted, click on D1. There should be a small black
square at the bottom right corner; place the mouse pointer on it, click and
drag it down as far as the dates/times in Column A and release.

The choices "active" and/or "ended" can be changed to whatever you want
to show in D1 to indicate the event was still in progress; just delete what's
between the " " s and substitute your choices. Also, if you don't won't to
see the entries in Col. E , right click on the Col. E header and on the menu
that pops up, click on " Hide ".

The second option would be to do a conditional format for the cell(s) in
column A.

Click cell A1, then, on the Menu bar at the top of the spreadsheet, click on
Format, Conditional Formatting, click the first box under "Condition 1" and
click on "Formula is". In the adjacent box, type in B1-A1>=E1-A1 .
Drop down to the "Cell Style" box , click on the "New Style" button. The
window that pops up will show "Untitled1" as the current name; clear this
cell and enter a name of your choice. Then click on the "Background" tab and
pick a color to indicate an event still in progress. Click the "OK" button at the
bottom. Then click the "OK" button on the Conditional Formatting window.

If A1 is not already highlighted, click on it. On the toolbar at the top of the
spreadsheet, find the "paintbrush" icon. Click on it. Bring the mouse pointer to
cell A2 (the mouse pointer should have changed to an icon of a "paint can"
tipping over). Click and drag down to the last entry in column A. That will have
put the just completed Conditional Format style in the other cells in Col. A.

scsisys

[/code]
 Code:
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum