Re: HELP with this function

I have this function which is to look at the date represented by: =C8-1, this
C8 represents the last day of the month, but can be a few days past the end
of the month. How do I, can I get this function to return a value equal the
previous month Or month ended if we are a couple of days past the month end?
Example, April 30, 2005 is he end of month, however, the spreadsheet is
pulled on May 2, 3or 4. I still want only the month value of April, 2005
(which is the month just completed) and NOT May, 2005. Moving forward, I'd
like to same approach for May, June and so on, and so on! How can this be
done! Is it even oissible? If so, HOW???

Thanks, guys!

Check out the =eomonth() function in help
=eomonth(-1) gives the last day of the last month

"Jay" wrote:

> I have this function which is to look at the date represented by: =C8-1, this
> C8 represents the last day of the month, but can be a few days past the end
> of the month. How do I, can I get this function to return a value equal the
> previous month Or month ended if we are a couple of days past the month end?
> Example, April 30, 2005 is he end of month, however, the spreadsheet is
> pulled on May 2, 3or 4. I still want only the month value of April, 2005
> (which is the month just completed) and NOT May, 2005. Moving forward, I'd
> like to same approach for May, June and so on, and so on! How can this be
> done! Is it even oissible? If so, HOW???
>
> Thanks, guys!

Actually, what I need is more like, if it's 6:59:59 A.M. on the first of the
current month roll it back the previous month, however, if it's 7:00:00 A.M
on the first of the month, then, add this value to the new/current month.
The reason is that the cut off of the previous month is at 6:59:59 A.M. and
the values for the new/current month begin at 7:00:00 A.M. Hope this makes
sense. If there are questions related to this, post back!

Thanks.



"bj" wrote:

> Check out the =eomonth() function in help
> =eomonth(-1) gives the last day of the last month
>
> "Jay" wrote:
>
> > I have this function which is to look at the date represented by: =C8-1, this
> > C8 represents the last day of the month, but can be a few days past the end
> > of the month. How do I, can I get this function to return a value equal the
> > previous month Or month ended if we are a couple of days past the month end?
> > Example, April 30, 2005 is he end of month, however, the spreadsheet is
> > pulled on May 2, 3or 4. I still want only the month value of April, 2005
> > (which is the month just completed) and NOT May, 2005. Moving forward, I'd
> > like to same approach for May, June and so on, and so on! How can this be
> > done! Is it even oissible? If so, HOW???
> >
> > Thanks, guys!

I assume there is a cell with the entered date/time value as a number (A1)
=month(a1-(7/24)
You may have to play with the 7/24 to get it to change exactly when you want.

"Jay" wrote:

> Actually, what I need is more like, if it's 6:59:59 A.M. on the first of the
> current month roll it back the previous month, however, if it's 7:00:00 A.M
> on the first of the month, then, add this value to the new/current month.
> The reason is that the cut off of the previous month is at 6:59:59 A.M. and
> the values for the new/current month begin at 7:00:00 A.M. Hope this makes
> sense. If there are questions related to this, post back!
>
> Thanks.
>
>
>
> "bj" wrote:
>
> > Check out the =eomonth() function in help
> > =eomonth(-1) gives the last day of the last month
> >
> > "Jay" wrote:
> >
> > > I have this function which is to look at the date represented by: =C8-1, this
> > > C8 represents the last day of the month, but can be a few days past the end
> > > of the month. How do I, can I get this function to return a value equal the
> > > previous month Or month ended if we are a couple of days past the month end?
> > > Example, April 30, 2005 is he end of month, however, the spreadsheet is
> > > pulled on May 2, 3or 4. I still want only the month value of April, 2005
> > > (which is the month just completed) and NOT May, 2005. Moving forward, I'd
> > > like to same approach for May, June and so on, and so on! How can this be
> > > done! Is it even oissible? If so, HOW???
> > >
> > > Thanks, guys!

Thanks for that snippet. However, it still does not do what I want. Maybe I'm
missing something! Any time prior to and up till 6:59:59 A.M. on the first of
the month rolls back to the previous month, Any thing from 7:00:00 A.M. on
the first of the month gets pulled into the current month. Maybe, I was not
clear enough, hope this helps a bit!




"bj" wrote:

> I assume there is a cell with the entered date/time value as a number (A1)
> =month(a1-(7/24)
> You may have to play with the 7/24 to get it to change exactly when you want.
>
> "Jay" wrote:
>
> > Actually, what I need is more like, if it's 6:59:59 A.M. on the first of the
> > current month roll it back the previous month, however, if it's 7:00:00 A.M
> > on the first of the month, then, add this value to the new/current month.
> > The reason is that the cut off of the previous month is at 6:59:59 A.M. and
> > the values for the new/current month begin at 7:00:00 A.M. Hope this makes
> > sense. If there are questions related to this, post back!
> >
> > Thanks.
> >
> >
> >
> > "bj" wrote:
> >
> > > Check out the =eomonth() function in help
> > > =eomonth(-1) gives the last day of the last month
> > >
> > > "Jay" wrote:
> > >
> > > > I have this function which is to look at the date represented by: =C8-1, this
> > > > C8 represents the last day of the month, but can be a few days past the end
> > > > of the month. How do I, can I get this function to return a value equal the
> > > > previous month Or month ended if we are a couple of days past the month end?
> > > > Example, April 30, 2005 is he end of month, however, the spreadsheet is
> > > > pulled on May 2, 3or 4. I still want only the month value of April, 2005
> > > > (which is the month just completed) and NOT May, 2005. Moving forward, I'd
> > > > like to same approach for May, June and so on, and so on! How can this be
> > > > done! Is it even oissible? If so, HOW???
> > > >
> > > > Thanks, guys!

Actually I thought his would do what yoiu asked.
if the cell with the date time information has 7 hours subtracted from it
and was initially less than 7 AM , (7/24 // .291667) the reported date should
be that of the previous day ; thus 1 June 6:59:59 AM - seven hours should be
31 May 11:59:59 and the equation should give May as the result for any entry
from 1 May 7Am to 1 June 6:59:59.
I do appologise for leaving the end parenthasis off the equation.

If I am still misunderstanding what you want, please try to explain it to
me again.

"Jay" wrote:

> Thanks for that snippet. However, it still does not do what I want. Maybe I'm
> missing something! Any time prior to and up till 6:59:59 A.M. on the first of
> the month rolls back to the previous month, Any thing from 7:00:00 A.M. on
> the first of the month gets pulled into the current month. Maybe, I was not
> clear enough, hope this helps a bit!
>
>
>
>
> "bj" wrote:
>
> > I assume there is a cell with the entered date/time value as a number (A1)
> > =month(a1-(7/24)
> > You may have to play with the 7/24 to get it to change exactly when you want.
> >
> > "Jay" wrote:
> >
> > > Actually, what I need is more like, if it's 6:59:59 A.M. on the first of the
> > > current month roll it back the previous month, however, if it's 7:00:00 A.M
> > > on the first of the month, then, add this value to the new/current month.
> > > The reason is that the cut off of the previous month is at 6:59:59 A.M. and
> > > the values for the new/current month begin at 7:00:00 A.M. Hope this makes
> > > sense. If there are questions related to this, post back!
> > >
> > > Thanks.
> > >
> > >
> > >
> > > "bj" wrote:
> > >
> > > > Check out the =eomonth() function in help
> > > > =eomonth(-1) gives the last day of the last month
> > > >
> > > > "Jay" wrote:
> > > >
> > > > > I have this function which is to look at the date represented by: =C8-1, this
> > > > > C8 represents the last day of the month, but can be a few days past the end
> > > > > of the month. How do I, can I get this function to return a value equal the
> > > > > previous month Or month ended if we are a couple of days past the month end?
> > > > > Example, April 30, 2005 is he end of month, however, the spreadsheet is
> > > > > pulled on May 2, 3or 4. I still want only the month value of April, 2005
> > > > > (which is the month just completed) and NOT May, 2005. Moving forward, I'd
> > > > > like to same approach for May, June and so on, and so on! How can this be
> > > > > done! Is it even oissible? If so, HOW???
> > > > >
> > > > > Thanks, guys!

Okay, say the day is May 1st, at 6:59:59 A.M. I'd like to show a value of
April or month 4. If the the date/time is May 1st, at 7:00:00 A. M. I'd like
to show a value of May or month 5. That's what I meant by rolling the value
into the previous month or current month based on the date/time. Everything
that happens on the first of the month up to 6:59:59 A.M. get put into the
previous month, everything after 7:00:00 A.M. gets put into the current
month. Hope this helps, sorry if I seam to ramble a bit! Thanks again for
your input!

"bj" wrote:

> Actually I thought his would do what yoiu asked.
> if the cell with the date time information has 7 hours subtracted from it
> and was initially less than 7 AM , (7/24 // .291667) the reported date should
> be that of the previous day ; thus 1 June 6:59:59 AM - seven hours should be
> 31 May 11:59:59 and the equation should give May as the result for any entry
> from 1 May 7Am to 1 June 6:59:59.
> I do appologise for leaving the end parenthasis off the equation.
>
> If I am still misunderstanding what you want, please try to explain it to
> me again.
>
> "Jay" wrote:
>
> > Thanks for that snippet. However, it still does not do what I want. Maybe I'm
> > missing something! Any time prior to and up till 6:59:59 A.M. on the first of
> > the month rolls back to the previous month, Any thing from 7:00:00 A.M. on
> > the first of the month gets pulled into the current month. Maybe, I was not
> > clear enough, hope this helps a bit!
> >
> >
> >
> >
> > "bj" wrote:
> >
> > > I assume there is a cell with the entered date/time value as a number (A1)
> > > =month(a1-(7/24)
> > > You may have to play with the 7/24 to get it to change exactly when you want.
> > >
> > > "Jay" wrote:
> > >
> > > > Actually, what I need is more like, if it's 6:59:59 A.M. on the first of the
> > > > current month roll it back the previous month, however, if it's 7:00:00 A.M
> > > > on the first of the month, then, add this value to the new/current month.
> > > > The reason is that the cut off of the previous month is at 6:59:59 A.M. and
> > > > the values for the new/current month begin at 7:00:00 A.M. Hope this makes
> > > > sense. If there are questions related to this, post back!
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > Check out the =eomonth() function in help
> > > > > =eomonth(-1) gives the last day of the last month
> > > > >
> > > > > "Jay" wrote:
> > > > >
> > > > > > I have this function which is to look at the date represented by: =C8-1, this
> > > > > > C8 represents the last day of the month, but can be a few days past the end
> > > > > > of the month. How do I, can I get this function to return a value equal the
> > > > > > previous month Or month ended if we are a couple of days past the month end?
> > > > > > Example, April 30, 2005 is he end of month, however, the spreadsheet is
> > > > > > pulled on May 2, 3or 4. I still want only the month value of April, 2005
> > > > > > (which is the month just completed) and NOT May, 2005. Moving forward, I'd
> > > > > > like to same approach for May, June and so on, and so on! How can this be
> > > > > > done! Is it even oissible? If so, HOW???
> > > > > >
> > > > > > Thanks, guys!

What do you get with the equation
=month(A1-.29166) and
=month(A1-.291667)
when A1 has the date time info?
My equation does what I think you are asking.
If you can show me where I am wrong, then I may be able to come up with a
different answer.

does your Cell have time only, or date time.
What is the value of your Cell when formated General?
for May 1, 6:59:59

"Jay" wrote:

> Okay, say the day is May 1st, at 6:59:59 A.M. I'd like to show a value of
> April or month 4. If the the date/time is May 1st, at 7:00:00 A. M. I'd like
> to show a value of May or month 5. That's what I meant by rolling the value
> into the previous month or current month based on the date/time. Everything
> that happens on the first of the month up to 6:59:59 A.M. get put into the
> previous month, everything after 7:00:00 A.M. gets put into the current
> month. Hope this helps, sorry if I seam to ramble a bit! Thanks again for
> your input!
>
> "bj" wrote:
>
> > Actually I thought his would do what yoiu asked.
> > if the cell with the date time information has 7 hours subtracted from it
> > and was initially less than 7 AM , (7/24 // .291667) the reported date should
> > be that of the previous day ; thus 1 June 6:59:59 AM - seven hours should be
> > 31 May 11:59:59 and the equation should give May as the result for any entry
> > from 1 May 7Am to 1 June 6:59:59.
> > I do appologise for leaving the end parenthasis off the equation.
> >
> > If I am still misunderstanding what you want, please try to explain it to
> > me again.
> >
> > "Jay" wrote:
> >
> > > Thanks for that snippet. However, it still does not do what I want. Maybe I'm
> > > missing something! Any time prior to and up till 6:59:59 A.M. on the first of
> > > the month rolls back to the previous month, Any thing from 7:00:00 A.M. on
> > > the first of the month gets pulled into the current month. Maybe, I was not
> > > clear enough, hope this helps a bit!
> > >
> > >
> > >
> > >
> > > "bj" wrote:
> > >
> > > > I assume there is a cell with the entered date/time value as a number (A1)
> > > > =month(a1-(7/24)
> > > > You may have to play with the 7/24 to get it to change exactly when you want.
> > > >
> > > > "Jay" wrote:
> > > >
> > > > > Actually, what I need is more like, if it's 6:59:59 A.M. on the first of the
> > > > > current month roll it back the previous month, however, if it's 7:00:00 A.M
> > > > > on the first of the month, then, add this value to the new/current month.
> > > > > The reason is that the cut off of the previous month is at 6:59:59 A.M. and
> > > > > the values for the new/current month begin at 7:00:00 A.M. Hope this makes
> > > > > sense. If there are questions related to this, post back!
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > Check out the =eomonth() function in help
> > > > > > =eomonth(-1) gives the last day of the last month
> > > > > >
> > > > > > "Jay" wrote:
> > > > > >
> > > > > > > I have this function which is to look at the date represented by: =C8-1, this
> > > > > > > C8 represents the last day of the month, but can be a few days past the end
> > > > > > > of the month. How do I, can I get this function to return a value equal the
> > > > > > > previous month Or month ended if we are a couple of days past the month end?
> > > > > > > Example, April 30, 2005 is he end of month, however, the spreadsheet is
> > > > > > > pulled on May 2, 3or 4. I still want only the month value of April, 2005
> > > > > > > (which is the month just completed) and NOT May, 2005. Moving forward, I'd
> > > > > > > like to same approach for May, June and so on, and so on! How can this be
> > > > > > > done! Is it even oissible? If so, HOW???
> > > > > > >
> > > > > > > Thanks, guys!