Help w/formulas - minor complication

Hi there -

ok, I am working on a VERY LONG tedious workbook for my office. I am a
beginner to formulas, in fact - the only thing I know how to do (and
needed to do, I thought, for this workbook) was display text from
another sheet.

I have a main sheet called: Info, and then I have several sheets that
follow and all of them pull some information from the Info Sheet. It's
to help us quickly fill out several forms that my company requires, but
seem redundant - so now - we'll be able to fill out the Info sheet and
all the other forms will pretty much fill themselves out. They all
relate to projects we have going on for the week.

Sorry for giving too much info, but I have to describe it to explain
the problem.

First I need to just give an example of what kinds of scenarios are on
the Info sheet so the problem makes sense.

I'm having a problem with jobs that run on multiple days. Because I
have some forms that display the ENTIRE project information (running
single or multiple days) and some forms that display DAY ONLY info,
meaning what happens the next day should not be on that sheet.

Example 1 for one single project (from Info):
Event 1 date - Monday, August 30, 2004
Event 1 time - 6pm
Event 2 date - Monday, August 30, 2004
Event 2 time - 8pm

Great . . . so on the sheets where I need what happens on that day -
fine - my formula displays the info from Info for Event 1 time and
Event 2 time (which is great because they're both on the same day)

But say my job runs on 2 dates . . .
Example 2 for one single project (from Info):
Event 1 date - Monday, August 30, 2004
Event 1 time - 6pm
Event 2 date - Tuesday, August 31, 2004
Event 2 time - 8pm

Now - on my sheet that shows me Event times I've got 6pm and 8pm -
which is inaccurate - because 8pm is the NEXT day.

So . . . can anyone think of something creative? Because I've spent a
billion hours on this and I've just now realized that I have a huge
problem.

I feel like there must be some sort of "If""Then" formula thing that
would relate to the dates on the sheet. But I just wouldn't know.

Thanks!
Jennifer


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

Custom Format the date cells as:- dddd, mmmm, d, yyyy

A date entered as mm/dd/yyyy or in Britain dd/mm/yy yy ie for your 1st
date 08/30/2004 or 30/08/2004 and it will show up as Monday, August 30, 2004

Now if you simply subtract the Even 2 date from the Event 1 one you will be
given the number of days. You may require to add 1 to the answer if you
require the two dates you give to show 2 days instead of 1.

HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"rsjennifer " wrote in message
...
Hi there -

ok, I am working on a VERY LONG tedious workbook for my office. I am a
beginner to formulas, in fact - the only thing I know how to do (and
needed to do, I thought, for this workbook) was display text from
another sheet.

I have a main sheet called: Info, and then I have several sheets that
follow and all of them pull some information from the Info Sheet. It's
to help us quickly fill out several forms that my company requires, but
seem redundant - so now - we'll be able to fill out the Info sheet and
all the other forms will pretty much fill themselves out. They all
relate to projects we have going on for the week.

Sorry for giving too much info, but I have to describe it to explain
the problem.

First I need to just give an example of what kinds of scenarios are on
the Info sheet so the problem makes sense.

I'm having a problem with jobs that run on multiple days. Because I
have some forms that display the ENTIRE project information (running
single or multiple days) and some forms that display DAY ONLY info,
meaning what happens the next day should not be on that sheet.

Example 1 for one single project (from Info):
Event 1 date - Monday, August 30, 2004
Event 1 time - 6pm
Event 2 date - Monday, August 30, 2004
Event 2 time - 8pm

Great . . . so on the sheets where I need what happens on that day -
fine - my formula displays the info from Info for Event 1 time and
Event 2 time (which is great because they're both on the same day)

But say my job runs on 2 dates . . .
Example 2 for one single project (from Info):
Event 1 date - Monday, August 30, 2004
Event 1 time - 6pm
Event 2 date - Tuesday, August 31, 2004
Event 2 time - 8pm

Now - on my sheet that shows me Event times I've got 6pm and 8pm -
which is inaccurate - because 8pm is the NEXT day.

So . . . can anyone think of something creative? Because I've spent a
billion hours on this and I've just now realized that I have a huge
problem.

I feel like there must be some sort of "If""Then" formula thing that
would relate to the dates on the sheet. But I just wouldn't know.

Thanks!
Jennifer


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

Thanks for trying to help Sandy. Except I don't see how that helps my
problem at all. My problem is not the way the text displays.

My problem is that sometimes Session 1 and Session 2 are on the same
day and sometimes Session 1 and Session 2 are on different days.

And when they're on the same day, I need them to display in one cell on
my sheet. But when they're on different days, I need the one that
corresponds with my day to show up and NOT the other one. But I need
one formula for that cell.

If your suggestion helps me with that . . . please explain how, because
I don't get it.

Thanks!
Jennifer


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

Hi Jennifer,
Unfortunately your question and or data are probably ambiguous
unless a person is really working 22, 48, 72 hours at a time,
or you are just tracking a project. In which case I doubt few would
accept a project after the beginning of a workday.

Take a look at http://www.mvps.org/dmcritchie/excel/datetime.htm
and see if that gives you any additional insight, such as core hours.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"rsjennifer " wrote in message ...
Thanks for trying to help Sandy. Except I don't see how that helps my
problem at all. My problem is not the way the text displays.

My problem is that sometimes Session 1 and Session 2 are on the same
day and sometimes Session 1 and Session 2 are on different days.

And when they're on the same day, I need them to display in one cell on
my sheet. But when they're on different days, I need the one that
corresponds with my day to show up and NOT the other one. But I need
one formula for that cell.

If your suggestion helps me with that . . . please explain how, because
I don't get it.

Thanks!
Jennifer


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

Hi Jennifer,

There are better ways of doing what you want so I would definately take a
look at the link that David provided. In the mean time to get either both
times in the same cell if they are on the same day or only the end time if
it is a different day, with the data set up as I previously described try:

=IF(A9=A10,TEXT(B9,"h:mm AM/PM")&" - "&TEXT(B10,"h:mm AM/PM"),TEXT(B10,"h:mm
AM/PM"))

Note that the times MUST be entered a XL time - what you posted looks like
text entries.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

"rsjennifer " wrote in message
...
Thanks for trying to help Sandy. Except I don't see how that helps my
problem at all. My problem is not the way the text displays.

My problem is that sometimes Session 1 and Session 2 are on the same
day and sometimes Session 1 and Session 2 are on different days.

And when they're on the same day, I need them to display in one cell on
my sheet. But when they're on different days, I need the one that
corresponds with my day to show up and NOT the other one. But I need
one formula for that cell.

If your suggestion helps me with that . . . please explain how, because
I don't get it.

Thanks!
Jennifer


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