Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jay
 
Posts: n/a
Default 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!
  #2   Report Post  
bj
 
Posts: n/a
Default

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!

  #3   Report Post  
Jay
 
Posts: n/a
Default

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!

  #4   Report Post  
bj
 
Posts: n/a
Default

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!

  #5   Report Post  
Jay
 
Posts: n/a
Default

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!



  #6   Report Post  
bj
 
Posts: n/a
Default

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!

  #7   Report Post  
Jay
 
Posts: n/a
Default

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!

  #8   Report Post  
bj
 
Posts: n/a
Default

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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
Need a ISWorkday Function -- Any Ideas Mark Excel Worksheet Functions 5 March 29th 05 01:58 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM


All times are GMT +1. The time now is 12:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"