Help w/formulas - minor complication

[rsjennifer]

Hi there -

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

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

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

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

I'm having a problem with jobs that run on multiple days. Because
have some forms that display the ENTIRE project information (runnin
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 an
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
billion hours on this and I've just now realized that I have a hug
problem.

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

Thanks!
Jennife

 

[Sandy Mann]

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

 

[rsjennifer]

Thanks for trying to help Sandy. Except I don't see how that helps m
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 sam
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 o
my sheet. But when they're on different days, I need the one tha
corresponds with my day to show up and NOT the other one. But I nee
one formula for that cell.

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

Thanks!
Jennife

 

[David McRitchie]

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.

 

[Sandy Mann]

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