Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with date range
Hi, all. I'm trying to do something that's probably pretty simple, but I
don't know how to do it. I've got a column of dates in my SS. What I want to do is if a date in the column falls between a certain date range, then in the column next to the date it gives a two-digit year. For example, 12/31/2005 falls between 7/1/2005 and 7/31/2006, so in the column next to "12/31/2005" it would say "06" or "2006" or something like that. I want to be able to do this with several date ranges. (I'm basically trying to translate dates in fiscal years.) Can anyone give me some tips? Thanks in advcance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with date range
I got a solution by creating a table of dates and their associated
fiscal years. For instance in A1 I have 7/1/2005; in B1I have 7/31/2006; in C1 I have 2006. Row 2 is for fiscal 2007, row 3 for 2008 and so on thru row 6 (fiscal 2011). In cell B9 I have the date 12/31/2005; in c9 I have the formula =SUMPRODUCT(--(B9=$A$1:$A$6),--(B9<=$B$1:$B$6),$C$1:$C$6) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with date range
Hi
From the dates given, it looks like a 13 month year!! I suspect you either meant 7/1/2005 and 6/30/2006 or 8/1/2005 and 7/31/2006 Taking the latter case, and assuming your dates are in column A, in column B =YEAR(A2)-(MONTH(A2)<8) Change the 8 to a 7 if the Year begins in July rather than August. If you only want to show a 2 digit year, format the cells as yy Regards Roger Govier RJohnson701CTS wrote: Hi, all. I'm trying to do something that's probably pretty simple, but I don't know how to do it. I've got a column of dates in my SS. What I want to do is if a date in the column falls between a certain date range, then in the column next to the date it gives a two-digit year. For example, 12/31/2005 falls between 7/1/2005 and 7/31/2006, so in the column next to "12/31/2005" it would say "06" or "2006" or something like that. I want to be able to do this with several date ranges. (I'm basically trying to translate dates in fiscal years.) Can anyone give me some tips? Thanks in advcance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with date range
You could probably use a formula that returns the year.
It could be as simple as =year(a2) But if your fiscal year isn't from Jan to Dec, you may want to post the dates. In fact, if you post your rules, you could even get a formula for fiscal quarter (just in case you ever need it). RJohnson701CTS wrote: Hi, all. I'm trying to do something that's probably pretty simple, but I don't know how to do it. I've got a column of dates in my SS. What I want to do is if a date in the column falls between a certain date range, then in the column next to the date it gives a two-digit year. For example, 12/31/2005 falls between 7/1/2005 and 7/31/2006, so in the column next to "12/31/2005" it would say "06" or "2006" or something like that. I want to be able to do this with several date ranges. (I'm basically trying to translate dates in fiscal years.) Can anyone give me some tips? Thanks in advcance! -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with date range
Ooooh. I should have read your post better.
Dave Peterson wrote: You could probably use a formula that returns the year. It could be as simple as =year(a2) But if your fiscal year isn't from Jan to Dec, you may want to post the dates. In fact, if you post your rules, you could even get a formula for fiscal quarter (just in case you ever need it). RJohnson701CTS wrote: Hi, all. I'm trying to do something that's probably pretty simple, but I don't know how to do it. I've got a column of dates in my SS. What I want to do is if a date in the column falls between a certain date range, then in the column next to the date it gives a two-digit year. For example, 12/31/2005 falls between 7/1/2005 and 7/31/2006, so in the column next to "12/31/2005" it would say "06" or "2006" or something like that. I want to be able to do this with several date ranges. (I'm basically trying to translate dates in fiscal years.) Can anyone give me some tips? Thanks in advcance! -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with date range
DOH! You're quite right, Roger. I meant to say 6/30/2006! I tired using
your formula, but it actually gives me the year prior to what it should be, i.e., 6/30/2006 returns "2005) with formula "=YEAR(J2)-(MONTH(J2)<7)" My dates are in column J... Many, many thanks! -RJ "Roger Govier" wrote: Hi From the dates given, it looks like a 13 month year!! I suspect you either meant 7/1/2005 and 6/30/2006 or 8/1/2005 and 7/31/2006 Taking the latter case, and assuming your dates are in column A, in column B =YEAR(A2)-(MONTH(A2)<8) Change the 8 to a 7 if the Year begins in July rather than August. If you only want to show a 2 digit year, format the cells as yy Regards Roger Govier RJohnson701CTS wrote: Hi, all. I'm trying to do something that's probably pretty simple, but I don't know how to do it. I've got a column of dates in my SS. What I want to do is if a date in the column falls between a certain date range, then in the column next to the date it gives a two-digit year. For example, 12/31/2005 falls between 7/1/2005 and 7/31/2006, so in the column next to "12/31/2005" it would say "06" or "2006" or something like that. I want to be able to do this with several date ranges. (I'm basically trying to translate dates in fiscal years.) Can anyone give me some tips? Thanks in advcance! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with date range
Hi
I read it the wrong way round Change to =YEAR(A19)+(MONTH(A19)6) Leave the format as General Regards Roger Govier RJohnsonHC wrote: DOH! You're quite right, Roger. I meant to say 6/30/2006! I tired using your formula, but it actually gives me the year prior to what it should be, i.e., 6/30/2006 returns "2005) with formula "=YEAR(J2)-(MONTH(J2)<7)" My dates are in column J... Many, many thanks! -RJ "Roger Govier" wrote: Hi From the dates given, it looks like a 13 month year!! I suspect you either meant 7/1/2005 and 6/30/2006 or 8/1/2005 and 7/31/2006 Taking the latter case, and assuming your dates are in column A, in column B =YEAR(A2)-(MONTH(A2)<8) Change the 8 to a 7 if the Year begins in July rather than August. If you only want to show a 2 digit year, format the cells as yy Regards Roger Govier RJohnson701CTS wrote: Hi, all. I'm trying to do something that's probably pretty simple, but I don't know how to do it. I've got a column of dates in my SS. What I want to do is if a date in the column falls between a certain date range, then in the column next to the date it gives a two-digit year. For example, 12/31/2005 falls between 7/1/2005 and 7/31/2006, so in the column next to "12/31/2005" it would say "06" or "2006" or something like that. I want to be able to do this with several date ranges. (I'm basically trying to translate dates in fiscal years.) Can anyone give me some tips? Thanks in advcance! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with date range
Give the man a cigar! Thank you, sir! :-)
"Roger Govier" wrote: Hi I read it the wrong way round Change to =YEAR(A19)+(MONTH(A19)6) Leave the format as General Regards Roger Govier RJohnsonHC wrote: DOH! You're quite right, Roger. I meant to say 6/30/2006! I tired using your formula, but it actually gives me the year prior to what it should be, i.e., 6/30/2006 returns "2005) with formula "=YEAR(J2)-(MONTH(J2)<7)" My dates are in column J... Many, many thanks! -RJ "Roger Govier" wrote: Hi From the dates given, it looks like a 13 month year!! I suspect you either meant 7/1/2005 and 6/30/2006 or 8/1/2005 and 7/31/2006 Taking the latter case, and assuming your dates are in column A, in column B =YEAR(A2)-(MONTH(A2)<8) Change the 8 to a 7 if the Year begins in July rather than August. If you only want to show a 2 digit year, format the cells as yy Regards Roger Govier RJohnson701CTS wrote: Hi, all. I'm trying to do something that's probably pretty simple, but I don't know how to do it. I've got a column of dates in my SS. What I want to do is if a date in the column falls between a certain date range, then in the column next to the date it gives a two-digit year. For example, 12/31/2005 falls between 7/1/2005 and 7/31/2006, so in the column next to "12/31/2005" it would say "06" or "2006" or something like that. I want to be able to do this with several date ranges. (I'm basically trying to translate dates in fiscal years.) Can anyone give me some tips? Thanks in advcance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get an IF statement to pull a date range?? | Excel Worksheet Functions | |||
Date Range within one cell | New Users to Excel | |||
Date Range and calculation | Excel Worksheet Functions | |||
Update cell based on date range | Excel Discussion (Misc queries) | |||
Count cells based on date range in another column | New Users to Excel |