Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing date so you can use A value or B value in a formula
two date ranges (may 1 thru oct 31)(nov 1 thur apr 30). depending on the date
there is a different value giving. example, its sept 8 and the value i need is 184. If it was dec 25, the value i would need is 189. all of this needs to reference the date which i used in a different cell using =(today)). any help would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing date so you can use A value or B value in a formula
Problem not clear. Can you provide few rows of data and the result you want?
"v1rt8" wrote: two date ranges (may 1 thru oct 31)(nov 1 thur apr 30). depending on the date there is a different value giving. example, its sept 8 and the value i need is 184. If it was dec 25, the value i would need is 189. all of this needs to reference the date which i used in a different cell using =(today)). any help would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing date so you can use A value or B value in a formul
Sheelo, thank you for the interest.
Based on the date in cell H1 that has the formula =today(), cell H2 is where i need a formula for the following: Date ranges are from (May 1 - Oct 31) if cell H1 falls between these dates then the result in cell H2 displays 184. however if the date in cell H1 is between (Nov 1 - Apr 30) then the result in cell H2 displays 189. is there also something else i would need to change the cell H1 as far as formating. the numbers displayed are used later on in the spreadsheet. "Sheeloo" wrote: Problem not clear. Can you provide few rows of data and the result you want? "v1rt8" wrote: two date ranges (may 1 thru oct 31)(nov 1 thur apr 30). depending on the date there is a different value giving. example, its sept 8 and the value i need is 184. If it was dec 25, the value i would need is 189. all of this needs to reference the date which i used in a different cell using =(today)). any help would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing date so you can use A value or B value in a formul
Look at the problem in a slightly different way: If the current month is 4
and it is <11 then it must be in May-October. If it isn't 4 and <11, then it must be in the Nov-Apr time frame. So we could put this in H2: =IF(AND(MONTH(H1)4,MONTH(H1)<11),184,189) or even as =IF(AND(MONTH(NOW())4,MONTH(NOW())<11),184,189) Hope this helps. "v1rt8" wrote: Sheelo, thank you for the interest. Based on the date in cell H1 that has the formula =today(), cell H2 is where i need a formula for the following: Date ranges are from (May 1 - Oct 31) if cell H1 falls between these dates then the result in cell H2 displays 184. however if the date in cell H1 is between (Nov 1 - Apr 30) then the result in cell H2 displays 189. is there also something else i would need to change the cell H1 as far as formating. the numbers displayed are used later on in the spreadsheet. "Sheeloo" wrote: Problem not clear. Can you provide few rows of data and the result you want? "v1rt8" wrote: two date ranges (may 1 thru oct 31)(nov 1 thur apr 30). depending on the date there is a different value giving. example, its sept 8 and the value i need is 184. If it was dec 25, the value i would need is 189. all of this needs to reference the date which i used in a different cell using =(today)). any help would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing date so you can use A value or B value in a formul
I used the second suggestion and it worked perfectly. validated by changing
system time. I appreciate your quick and accurate reply thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing date so you can use A value or B value in a formul
Enter the following in A1 to A4;
30-May-2008 31-Oct-2008 1-Nov-2008 1-May-2009 Enter this in H2 =IF(AND(H1A1,H1<A3),184,IF(AND(H1A2,H1<A4),189," Not in range")) This will give you what you want in H2 by entering the date in H1. btw JLatham's solution is better than this but this might come handy in future. "v1rt8" wrote: Sheelo, thank you for the interest. Based on the date in cell H1 that has the formula =today(), cell H2 is where i need a formula for the following: Date ranges are from (May 1 - Oct 31) if cell H1 falls between these dates then the result in cell H2 displays 184. however if the date in cell H1 is between (Nov 1 - Apr 30) then the result in cell H2 displays 189. is there also something else i would need to change the cell H1 as far as formating. the numbers displayed are used later on in the spreadsheet. "Sheeloo" wrote: Problem not clear. Can you provide few rows of data and the result you want? "v1rt8" wrote: two date ranges (may 1 thru oct 31)(nov 1 thur apr 30). depending on the date there is a different value giving. example, its sept 8 and the value i need is 184. If it was dec 25, the value i would need is 189. all of this needs to reference the date which i used in a different cell using =(today)). any help would be appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing date so you can use A value or B value in a formul
Thank you also sheelo, it will come in handy
"Sheeloo" wrote: Enter the following in A1 to A4; 30-May-2008 31-Oct-2008 1-Nov-2008 1-May-2009 Enter this in H2 =IF(AND(H1A1,H1<A3),184,IF(AND(H1A2,H1<A4),189," Not in range")) This will give you what you want in H2 by entering the date in H1. btw JLatham's solution is better than this but this might come handy in future. "v1rt8" wrote: Sheelo, thank you for the interest. Based on the date in cell H1 that has the formula =today(), cell H2 is where i need a formula for the following: Date ranges are from (May 1 - Oct 31) if cell H1 falls between these dates then the result in cell H2 displays 184. however if the date in cell H1 is between (Nov 1 - Apr 30) then the result in cell H2 displays 189. is there also something else i would need to change the cell H1 as far as formating. the numbers displayed are used later on in the spreadsheet. "Sheeloo" wrote: Problem not clear. Can you provide few rows of data and the result you want? "v1rt8" wrote: two date ranges (may 1 thru oct 31)(nov 1 thur apr 30). depending on the date there is a different value giving. example, its sept 8 and the value i need is 184. If it was dec 25, the value i would need is 189. all of this needs to reference the date which i used in a different cell using =(today)). any help would be appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing date so you can use A value or B value in a formul
The formula I provided was specific to the request - and it is pretty much
limited to dividing the year into 2 parts. Yours is more 'generic' and adaptable to more situations and is also extensible to deal with more than just 2 possible conditions. "Sheeloo" wrote: Enter the following in A1 to A4; 30-May-2008 31-Oct-2008 1-Nov-2008 1-May-2009 Enter this in H2 =IF(AND(H1A1,H1<A3),184,IF(AND(H1A2,H1<A4),189," Not in range")) This will give you what you want in H2 by entering the date in H1. btw JLatham's solution is better than this but this might come handy in future. "v1rt8" wrote: Sheelo, thank you for the interest. Based on the date in cell H1 that has the formula =today(), cell H2 is where i need a formula for the following: Date ranges are from (May 1 - Oct 31) if cell H1 falls between these dates then the result in cell H2 displays 184. however if the date in cell H1 is between (Nov 1 - Apr 30) then the result in cell H2 displays 189. is there also something else i would need to change the cell H1 as far as formating. the numbers displayed are used later on in the spreadsheet. "Sheeloo" wrote: Problem not clear. Can you provide few rows of data and the result you want? "v1rt8" wrote: two date ranges (may 1 thru oct 31)(nov 1 thur apr 30). depending on the date there is a different value giving. example, its sept 8 and the value i need is 184. If it was dec 25, the value i would need is 189. all of this needs to reference the date which i used in a different cell using =(today)). any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing cells according to date | Excel Worksheet Functions | |||
Retaining date formatting in referencing cell | Excel Discussion (Misc queries) | |||
referencing todays date. | Excel Worksheet Functions | |||
Date referencing | Excel Worksheet Functions | |||
Pulling date from 1 ws to another while referencing 2 fields | Excel Discussion (Misc queries) |