OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Formula to check state between two timestamps

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
third
Newbie
Newbie


Joined: 27 Sep 2011
Posts: 1

PostPosted: Tue Sep 27, 2011 4:53 am    Post subject: [Solved] Formula to check state between two timestamps Reply with quote

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
Back to top
View user's profile Send private message
scsisys
OOo Advocate
OOo Advocate


Joined: 17 Dec 2009
Posts: 248

PostPosted: Tue Sep 27, 2011 11:14 am    Post subject: Reply with quote

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:
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group