Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
I am setting up a spreadsheet of investments. The first column (A, row 1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and so forth through the end of the year (Wednesday 12/31/2008). It takes a long time to set all that up. I'm now wondering if the dates can be automatically changed - say from Monday 01/21 to Monday 01/19 and the years change from 2008 to 2009? Can that be done without my having to input all the dates again for next year? Thanks for any help you can give? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
With the date in A1, you can put this formula in A2:
=A1+1 which will add one day on to the previous day. Copy this formula down and you will have all your dates for the whole year. You only need to change the date in A1 to have a knock-on effect on the other dates. Hope this helps. Pete On Jan 18, 5:27*pm, Dick C wrote: I am setting up a spreadsheet of investments. *The first column (A, row 1) has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and so forth through the end of the year (Wednesday 12/31/2008). *It takes a long time to set all that up. *I'm now wondering if the dates can be automatically changed - say from Monday 01/21 to Monday 01/19 and the years change from 2008 to 2009? *Can that be done without my having to input all the dates again for next year? *Thanks for any help you can give? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
Thanks for ur help Pete. The formula is very good if I don't have the name
of the days in the block or if I include Sat/Sunday. I need to have Monday 01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008 in A3 etc. I will only be using Monday through Friday as there are only 5 trading days. So picture all the days and dates in the A column (from 1/21/08 down through 12/31/2008. Then when next Jan arrives I want to change the dates to line up with the days of the week in 2009. For instance if I had started this year on Tuesday 01/01/2008, then is there an easy way for me to click a space to change to 01/01/2009 and have the day be Thursday - which the first falls on next year? This is difficult to explain I know and I'm just not sure it can be done. But give it a go and let me know if you think that it can. If not, it's OK I'll just type it all in again, but it sure takes a long, long time. "Pete_UK" wrote: With the date in A1, you can put this formula in A2: =A1+1 which will add one day on to the previous day. Copy this formula down and you will have all your dates for the whole year. You only need to change the date in A1 to have a knock-on effect on the other dates. Hope this helps. Pete On Jan 18, 5:27 pm, Dick C wrote: I am setting up a spreadsheet of investments. The first column (A, row 1) has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and so forth through the end of the year (Wednesday 12/31/2008). It takes a long time to set all that up. I'm now wondering if the dates can be automatically changed - say from Monday 01/21 to Monday 01/19 and the years change from 2008 to 2009? Can that be done without my having to input all the dates again for next year? Thanks for any help you can give? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
Try this formula instead in A2:
=IF(WEEKDAY(A1)=6,A1+3,A1+1) and then copy it down. This will give you the days in sequence, but will jump from Friday to the next Monday, so again, all you will need to do is change the date in A1 to the first working day of the year and the formulae will do the rest. Saves all that typing. Hope this helps. Pete On Jan 19, 1:15*am, Dick C wrote: Thanks for ur help Pete. *The formula is very good if I don't have the name of the days in the block or if I include Sat/Sunday. *I need to have Monday 01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008 in A3 etc. *I will only be using Monday through Friday as there are only 5 trading days. *So picture all the days and dates in the A column (from 1/21/08 down through 12/31/2008. *Then when next Jan arrives I want to change the dates to line up with the days of the week in 2009. *For instance if I had started this year on Tuesday 01/01/2008, then is there an easy way for me to click a space to change to 01/01/2009 and have the day be Thursday - which the first falls on next year? *This is difficult to explain I know and I'm just not sure it can be done. *But give it a go and let me know if you think that it can. *If not, it's OK I'll just type it all in again, but it sure takes a long, long time. * "Pete_UK" wrote: With the date in A1, you can put this formula in A2: =A1+1 which will add one day on to the previous day. Copy this formula down and you will have all your dates for the whole year. You only need to change the date in A1 to have a knock-on effect on the other dates. Hope this helps. Pete On Jan 18, 5:27 pm, Dick C wrote: I am setting up a spreadsheet of investments. *The first column (A, row 1) has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and so forth through the end of the year (Wednesday 12/31/2008). *It takes a long time to set all that up. *I'm now wondering if the dates can be automatically changed - say from Monday 01/21 to Monday 01/19 and the years change from 2008 to 2009? *Can that be done without my having to input all the dates again for next year? *Thanks for any help you can give?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
Dick,
Have just re-read your post. If you need to see both the day and the date in the cell, then you can do this with formatting - highlight column A, then click on Format | Cells | Number tab, click on Custom at the bottom of the list and type dddd mm/dd/yyyy. You might need to widen the column. You only need to enter the date as 01/01/09 in A1, and it will automatically show you the day as well. Hope this helps. Pete "Dick C" wrote in message ... Thanks for ur help Pete. The formula is very good if I don't have the name of the days in the block or if I include Sat/Sunday. I need to have Monday 01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008 in A3 etc. I will only be using Monday through Friday as there are only 5 trading days. So picture all the days and dates in the A column (from 1/21/08 down through 12/31/2008. Then when next Jan arrives I want to change the dates to line up with the days of the week in 2009. For instance if I had started this year on Tuesday 01/01/2008, then is there an easy way for me to click a space to change to 01/01/2009 and have the day be Thursday - which the first falls on next year? This is difficult to explain I know and I'm just not sure it can be done. But give it a go and let me know if you think that it can. If not, it's OK I'll just type it all in again, but it sure takes a long, long time. "Pete_UK" wrote: With the date in A1, you can put this formula in A2: =A1+1 which will add one day on to the previous day. Copy this formula down and you will have all your dates for the whole year. You only need to change the date in A1 to have a knock-on effect on the other dates. Hope this helps. Pete On Jan 18, 5:27 pm, Dick C wrote: I am setting up a spreadsheet of investments. The first column (A, row 1) has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and so forth through the end of the year (Wednesday 12/31/2008). It takes a long time to set all that up. I'm now wondering if the dates can be automatically changed - say from Monday 01/21 to Monday 01/19 and the years change from 2008 to 2009? Can that be done without my having to input all the dates again for next year? Thanks for any help you can give? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
Didn't work Pete. I have an older version of Excel. It's 97 but it did have
something close to dddd mm/dd/yyyy. Here's what I did. I typed in Monday 01/01/2008. Then, with my cursor still in that cell I clicked Format/Cell/Number Tab/Custom and selected dd/mm/yyyy. Then I tried to copy that to A2 and just got Monday 01/01/2008. "Pete_UK" wrote: Dick, Have just re-read your post. If you need to see both the day and the date in the cell, then you can do this with formatting - highlight column A, then click on Format | Cells | Number tab, click on Custom at the bottom of the list and type dddd mm/dd/yyyy. You might need to widen the column. You only need to enter the date as 01/01/09 in A1, and it will automatically show you the day as well. Hope this helps. Pete "Dick C" wrote in message ... Thanks for ur help Pete. The formula is very good if I don't have the name of the days in the block or if I include Sat/Sunday. I need to have Monday 01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008 in A3 etc. I will only be using Monday through Friday as there are only 5 trading days. So picture all the days and dates in the A column (from 1/21/08 down through 12/31/2008. Then when next Jan arrives I want to change the dates to line up with the days of the week in 2009. For instance if I had started this year on Tuesday 01/01/2008, then is there an easy way for me to click a space to change to 01/01/2009 and have the day be Thursday - which the first falls on next year? This is difficult to explain I know and I'm just not sure it can be done. But give it a go and let me know if you think that it can. If not, it's OK I'll just type it all in again, but it sure takes a long, long time. "Pete_UK" wrote: With the date in A1, you can put this formula in A2: =A1+1 which will add one day on to the previous day. Copy this formula down and you will have all your dates for the whole year. You only need to change the date in A1 to have a knock-on effect on the other dates. Hope this helps. Pete On Jan 18, 5:27 pm, Dick C wrote: I am setting up a spreadsheet of investments. The first column (A, row 1) has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and so forth through the end of the year (Wednesday 12/31/2008). It takes a long time to set all that up. I'm now wondering if the dates can be automatically changed - say from Monday 01/21 to Monday 01/19 and the years change from 2008 to 2009? Can that be done without my having to input all the dates again for next year? Thanks for any help you can give? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
In A2: 1/21/2008 (format cell as: dddd, m/d/yyyy)
In A3: =WORKDAY(A2,1) copy from A3 down as far as needed "Dick C" wrote: Didn't work Pete. I have an older version of Excel. It's 97 but it did have something close to dddd mm/dd/yyyy. Here's what I did. I typed in Monday 01/01/2008. Then, with my cursor still in that cell I clicked Format/Cell/Number Tab/Custom and selected dd/mm/yyyy. Then I tried to copy that to A2 and just got Monday 01/01/2008. "Pete_UK" wrote: Dick, Have just re-read your post. If you need to see both the day and the date in the cell, then you can do this with formatting - highlight column A, then click on Format | Cells | Number tab, click on Custom at the bottom of the list and type dddd mm/dd/yyyy. You might need to widen the column. You only need to enter the date as 01/01/09 in A1, and it will automatically show you the day as well. Hope this helps. Pete "Dick C" wrote in message ... Thanks for ur help Pete. The formula is very good if I don't have the name of the days in the block or if I include Sat/Sunday. I need to have Monday 01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008 in A3 etc. I will only be using Monday through Friday as there are only 5 trading days. So picture all the days and dates in the A column (from 1/21/08 down through 12/31/2008. Then when next Jan arrives I want to change the dates to line up with the days of the week in 2009. For instance if I had started this year on Tuesday 01/01/2008, then is there an easy way for me to click a space to change to 01/01/2009 and have the day be Thursday - which the first falls on next year? This is difficult to explain I know and I'm just not sure it can be done. But give it a go and let me know if you think that it can. If not, it's OK I'll just type it all in again, but it sure takes a long, long time. "Pete_UK" wrote: With the date in A1, you can put this formula in A2: =A1+1 which will add one day on to the previous day. Copy this formula down and you will have all your dates for the whole year. You only need to change the date in A1 to have a knock-on effect on the other dates. Hope this helps. Pete On Jan 18, 5:27 pm, Dick C wrote: I am setting up a spreadsheet of investments. The first column (A, row 1) has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and so forth through the end of the year (Wednesday 12/31/2008). It takes a long time to set all that up. I'm now wondering if the dates can be automatically changed - say from Monday 01/21 to Monday 01/19 and the years change from 2008 to 2009? Can that be done without my having to input all the dates again for next year? Thanks for any help you can give? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
You should type the date only, i.e. 01/01/08.
THEN, you apply the custom formatting of dddd mm/dd/yyyy. It will show you Monday 01/01/2008. If you type in "Monday 01/01/2008" (without the quotes) then Excel will take this as a text value, and formatting will have no effect - and the formula in A2 et seq will not work. Hope this helps. Pete On Jan 19, 3:29*am, Dick C wrote: Didn't work Pete. *I have an older version of Excel. *It's 97 but it did have something close to dddd mm/dd/yyyy. *Here's what I did. *I typed in Monday 01/01/2008. *Then, with my cursor still in that cell I clicked Format/Cell/Number Tab/Custom and selected dd/mm/yyyy. *Then I tried to copy that to A2 and just got Monday 01/01/2008. * "Pete_UK" wrote: Dick, Have just re-read your post. If you need to see both the day and the date in the cell, then you can do this with formatting - highlight column A, then click on Format | Cells | Number tab, click on Custom at the bottom of the list and type dddd mm/dd/yyyy. You might need to widen the column. You only need to enter the date as 01/01/09 in A1, and it will automatically show you the day as well. Hope this helps. Pete "Dick C" wrote in message ... Thanks for ur help Pete. *The formula is very good if I don't have the name of the days in the block or if I include Sat/Sunday. *I need to have Monday 01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008 in A3 etc. *I will only be using Monday through Friday as there are only 5 trading days. *So picture all the days and dates in the A column (from 1/21/08 down through 12/31/2008. *Then when next Jan arrives I want to change the dates to line up with the days of the week in 2009. *For instance if I had started this year on Tuesday 01/01/2008, then is there an easy way for me to click a space to change to 01/01/2009 and have the day be Thursday - which the first falls on next year? *This is difficult to explain I know and I'm just not sure it can be done. *But give it a go and let me know if you think that it can. *If not, it's OK I'll just type it all in again, but it sure takes a long, long time. "Pete_UK" wrote: With the date in A1, you can put this formula in A2: =A1+1 which will add one day on to the previous day. Copy this formula down and you will have all your dates for the whole year. You only need to change the date in A1 to have a knock-on effect on the other dates. Hope this helps. Pete On Jan 18, 5:27 pm, Dick C wrote: I am setting up a spreadsheet of investments. *The first column (A, row 1) has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and so forth through the end of the year (Wednesday 12/31/2008). *It takes a long time to set all that up. *I'm now wondering if the dates can be automatically changed - say from Monday 01/21 to Monday 01/19 and the years change from 2008 to 2009? *Can that be done without my having to input all the dates again for next year? *Thanks for any help you can give?- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
Actually, it will show you Tuesday 01/01/08 after you apply the
formatting !! Pete On Jan 19, 12:45*pm, Pete_UK wrote: You should type the date only, i.e. 01/01/08. THEN, you apply the custom formatting of dddd mm/dd/yyyy. It will show you Monday 01/01/2008. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
Pete, I'm getting further along. Here's where I am. In A1 I have the date:
01/01/2008. Then I have applied the Format/Cell/Number Tab/Custom dddd mm/dd/yyyy. And that works perfectly just as you'd said it would. So that gives me something to work from. Then I went back through your other suggested formulas and have tried each one. The one that works is the very first one - so in A2 I have inserted the formula: =A1+1. And I copied that down through my spread sheet. And all the days and dates come out perfectly. When I change the year in A1 from 2008 to 2009 all the other columns change to 2009 as well as the days line up perfectly with those dates as well. That's just what I wanted. However ... and this is not a huge problem for me ..... I still have Saturday and Sunday each week. Now when I do the formulas for columns B, C, D and so forth to complete the rest of my spreadsheet the only difficulty is skipping over Saturday and Sunday because they are not active trading days. I can do that if I have to. I tried your formula: =IF(WEEKDAY(A1)=6,A1+3,A1+1) and I get some weird (""}}39449) stuff, and I've tried the other formula: =WORKDAY(A2,1) and what shows in A2 is #NAME. So I don't know what to do with that. Any other thoughts. We're almost there. -- DC "Pete_UK" wrote: Actually, it will show you Tuesday 01/01/08 after you apply the formatting !! Pete On Jan 19, 12:45 pm, Pete_UK wrote: You should type the date only, i.e. 01/01/08. THEN, you apply the custom formatting of dddd mm/dd/yyyy. It will show you Monday 01/01/2008. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
And when you got #NAME? from the WORKDAY function, and you looked in Excel
help for the WORKDAY function, what did it tell you? -- David Biddulph "DC" wrote in message ... Pete, I'm getting further along. Here's where I am. In A1 I have the date: 01/01/2008. Then I have applied the Format/Cell/Number Tab/Custom dddd mm/dd/yyyy. And that works perfectly just as you'd said it would. So that gives me something to work from. Then I went back through your other suggested formulas and have tried each one. The one that works is the very first one - so in A2 I have inserted the formula: =A1+1. And I copied that down through my spread sheet. And all the days and dates come out perfectly. When I change the year in A1 from 2008 to 2009 all the other columns change to 2009 as well as the days line up perfectly with those dates as well. That's just what I wanted. However ... and this is not a huge problem for me .... I still have Saturday and Sunday each week. Now when I do the formulas for columns B, C, D and so forth to complete the rest of my spreadsheet the only difficulty is skipping over Saturday and Sunday because they are not active trading days. I can do that if I have to. I tried your formula: =IF(WEEKDAY(A1)=6,A1+3,A1+1) and I get some weird (""}}39449) stuff, and I've tried the other formula: =WORKDAY(A2,1) and what shows in A2 is #NAME. So I don't know what to do with that. Any other thoughts. We're almost there. -- DC "Pete_UK" wrote: Actually, it will show you Tuesday 01/01/08 after you apply the formatting !! Pete On Jan 19, 12:45 pm, Pete_UK wrote: You should type the date only, i.e. 01/01/08. THEN, you apply the custom formatting of dddd mm/dd/yyyy. It will show you Monday 01/01/2008. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
The "weird" stuff, as you call it, is actually the serial number
underlying the date - Excel records dates internally as the number of elapsed days from some starting point (01/01/1900), so 39449 is just the number of elapsed days from that point. This explains how the A1+1 formula works - it just makes the next day in sequence. If you apply the custom formatting to A2 you should see Wednesday 01/02/2008. Highlight the complete column by clicking on the A at the top of the column and then do Format | Cells | Number tab | Custom and then set it up as dddd mm/dd/yyyy. Then if you put the formula: =IF(WEEKDAY(A1)=6,A1+3,A1+1) in A2 and copy it down, you will see the format you want for dates where weekends are omitted. Essentially this formula says if the date on the previous row is a Friday then add 3 to the date (jumping to the next Monday), otherwise just add one to the previous date (to make the next one in sequence). You should ensure that your date in A1 is for a working day. Hope this helps. Pete On Jan 19, 7:15*pm, DC wrote: Pete, I'm getting further along. *Here's where I am. *In A1 I have the date: 01/01/2008. *Then I have applied the Format/Cell/Number Tab/Custom dddd mm/dd/yyyy. *And that works perfectly just as you'd said it would. *So that gives me something to work from. *Then I went back through your other suggested formulas and have tried each one. *The one that works is the very first one - so in A2 I have inserted the formula: =A1+1. *And I copied that down through my spread sheet. *And all the days and dates come out perfectly. *When I change the year in A1 from 2008 to 2009 all the other columns change to 2009 as well as the days line up perfectly with those dates as well. * That's just what I wanted. *However ... and this is not a huge problem for me .... I still have Saturday and Sunday each week. *Now when I do the formulas for columns B, C, D and so forth to complete the rest of my spreadsheet the only difficulty is skipping over Saturday and Sunday because they are not active trading days. *I can do that if I have to. * I tried your formula: =IF(WEEKDAY(A1)=6,A1+3,A1+1) and I get some weird (""}}39449) stuff, and I've tried the other formula: =WORKDAY(A2,1) and what shows in A2 is #NAME. *So I don't know what to do with that. *Any other thoughts. *We're almost there. -- DC "Pete_UK" wrote: Actually, it will show you Tuesday 01/01/08 after you apply the formatting !! Pete On Jan 19, 12:45 pm, Pete_UK wrote: You should type the date only, i.e. 01/01/08. THEN, you apply the custom formatting of dddd mm/dd/yyyy. It will show you Monday 01/01/2008.- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
BINGO! That worked perfectly. Thanks for hanging in there with me and for
your expertise ... I really appreciate that. And maybe I'll learn some more macros for other projects. Thanks again "Pete_UK" wrote: The "weird" stuff, as you call it, is actually the serial number underlying the date - Excel records dates internally as the number of elapsed days from some starting point (01/01/1900), so 39449 is just the number of elapsed days from that point. This explains how the A1+1 formula works - it just makes the next day in sequence. If you apply the custom formatting to A2 you should see Wednesday 01/02/2008. Highlight the complete column by clicking on the A at the top of the column and then do Format | Cells | Number tab | Custom and then set it up as dddd mm/dd/yyyy. Then if you put the formula: =IF(WEEKDAY(A1)=6,A1+3,A1+1) in A2 and copy it down, you will see the format you want for dates where weekends are omitted. Essentially this formula says if the date on the previous row is a Friday then add 3 to the date (jumping to the next Monday), otherwise just add one to the previous date (to make the next one in sequence). You should ensure that your date in A1 is for a working day. Hope this helps. Pete On Jan 19, 7:15 pm, DC wrote: Pete, I'm getting further along. Here's where I am. In A1 I have the date: 01/01/2008. Then I have applied the Format/Cell/Number Tab/Custom dddd mm/dd/yyyy. And that works perfectly just as you'd said it would. So that gives me something to work from. Then I went back through your other suggested formulas and have tried each one. The one that works is the very first one - so in A2 I have inserted the formula: =A1+1. And I copied that down through my spread sheet. And all the days and dates come out perfectly. When I change the year in A1 from 2008 to 2009 all the other columns change to 2009 as well as the days line up perfectly with those dates as well. That's just what I wanted. However ... and this is not a huge problem for me .... I still have Saturday and Sunday each week. Now when I do the formulas for columns B, C, D and so forth to complete the rest of my spreadsheet the only difficulty is skipping over Saturday and Sunday because they are not active trading days. I can do that if I have to. I tried your formula: =IF(WEEKDAY(A1)=6,A1+3,A1+1) and I get some weird (""}}39449) stuff, and I've tried the other formula: =WORKDAY(A2,1) and what shows in A2 is #NAME. So I don't know what to do with that. Any other thoughts. We're almost there. -- DC "Pete_UK" wrote: Actually, it will show you Tuesday 01/01/08 after you apply the formatting !! Pete On Jan 19, 12:45 pm, Pete_UK wrote: You should type the date only, i.e. 01/01/08. THEN, you apply the custom formatting of dddd mm/dd/yyyy. It will show you Monday 01/01/2008.- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing dates and years with less keystrokes
Glad we finally got there, Dick, but these were simple formulae - not
macros !! Pete On Jan 19, 9:52*pm, Dick C wrote: BINGO! *That worked perfectly. *Thanks for hanging in there with me and for your expertise ... I really appreciate that. *And maybe I'll learn some more macros for other projects. *Thanks again "Pete_UK" wrote: The "weird" stuff, as you call it, is actually the serial number underlying the date - Excel records dates internally as the number of elapsed days from some starting point (01/01/1900), so 39449 is just the number of elapsed days from that point. This explains how the A1+1 formula works - it just makes the next day in sequence. If you apply the custom formatting to A2 you should see Wednesday 01/02/2008. Highlight the complete column by clicking on the A at the top of the column and then do Format | Cells | Number tab | Custom and then set it up as dddd mm/dd/yyyy. Then if you put the formula: =IF(WEEKDAY(A1)=6,A1+3,A1+1) in A2 and copy it down, you will see the format you want for dates where weekends are omitted. Essentially this formula says if the date on the previous row is a Friday then add 3 to the date (jumping to the next Monday), otherwise just add one to the previous date (to make the next one in sequence). You should ensure that your date in A1 is for a working day. Hope this helps. Pete On Jan 19, 7:15 pm, DC wrote: Pete, I'm getting further along. *Here's where I am. *In A1 I have the date: 01/01/2008. *Then I have applied the Format/Cell/Number Tab/Custom dddd mm/dd/yyyy. *And that works perfectly just as you'd said it would. *So that gives me something to work from. *Then I went back through your other suggested formulas and have tried each one. *The one that works is the very first one - so in A2 I have inserted the formula: =A1+1. *And I copied that down through my spread sheet. *And all the days and dates come out perfectly. *When I change the year in A1 from 2008 to 2009 all the other columns change to 2009 as well as the days line up perfectly with those dates as well.. * That's just what I wanted. *However ... and this is not a huge problem for me .... I still have Saturday and Sunday each week. *Now when I do the formulas for columns B, C, D and so forth to complete the rest of my spreadsheet the only difficulty is skipping over Saturday and Sunday because they are not active trading days. *I can do that if I have to. * I tried your formula: =IF(WEEKDAY(A1)=6,A1+3,A1+1) and I get some weird (""}}39449) stuff, and I've tried the other formula: =WORKDAY(A2,1) and what shows in A2 is #NAME. *So I don't know what to do with that. *Any other thoughts. *We're almost there. -- DC "Pete_UK" wrote: Actually, it will show you Tuesday 01/01/08 after you apply the formatting !! Pete On Jan 19, 12:45 pm, Pete_UK wrote: You should type the date only, i.e. 01/01/08. THEN, you apply the custom formatting of dddd mm/dd/yyyy. It will show you Monday 01/01/2008.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates = years old | Excel Discussion (Misc queries) | |||
Dates, Years and Seasons | Excel Worksheet Functions | |||
How do I subtract dates to get a number in years or years & month | Excel Discussion (Misc queries) | |||
Changing dates entered in 2005 to previous years | Excel Worksheet Functions | |||
i NEED THE DIFFERENCE OF DATES DOWN TO YEARS...IE 11/1/02-4/16/85. | Excel Worksheet Functions |