Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating monthly start date with a start date
I have a spreadsheet that I use to calculate dates for inventory supplies. I
have a formula that works great except for one function - I would like to be able to use the start date of any month (January, February, March etc.) in calculating what the next working day will be after entering 1/1/08, 2/1/08 etc. The formula I now use is =WORKDAY(C4,1,holidays) This formula does not choose the first working day correctly. January is correct when I insert 1/1/08, February also, with 2/1/08 but March 3/1/08 is on a Saturday. I would like my formula to also calculate the next start date of the month, even if I use 1/1/08 or 2/1/08 or 3/1/08 as a start date. i.e. C4: 3/1/08 =WORKDAY(U4,1,holidays) =WORKDAY(W4,1,holidays) copied to: C 40 - S 40 The month ends on the proper day. I am not sure this is possible, but I would love someone who could help me figure out how to do it. Thanks! Monique |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating monthly start date with a start date
On Fri, 19 Dec 2008 17:33:02 -0800, Monique
wrote: I have a spreadsheet that I use to calculate dates for inventory supplies. I have a formula that works great except for one function - I would like to be able to use the start date of any month (January, February, March etc.) in calculating what the next working day will be after entering 1/1/08, 2/1/08 etc. The formula I now use is =WORKDAY(C4,1,holidays) This formula does not choose the first working day correctly. January is correct when I insert 1/1/08, February also, with 2/1/08 but March 3/1/08 is on a Saturday. I would like my formula to also calculate the next start date of the month, even if I use 1/1/08 or 2/1/08 or 3/1/08 as a start date. i.e. C4: 3/1/08 =WORKDAY(U4,1,holidays) =WORKDAY(W4,1,holidays) copied to: C 40 - S 40 The month ends on the proper day. I am not sure this is possible, but I would love someone who could help me figure out how to do it. Thanks! Monique Perhaps I am not understanding your problem correctly, as I don't see how February would be correct using 2/1/08 in C4. What I understand is that, with some random date in C4, you want your formula to return the first working day of the month in C4. If that is correct, then another way of stating that is that you want to return the first working day after the last day of the month preceding the date in C4. To do that: =WORKDAY(C4-DAY(C4),1,holidays) --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating monthly start date with a start date
Hi Ron,
I couldn't get this to work in the C4 cell like I wanted to but I left it in C4 and put the date I wanted in B4. I changed the font color to white so the date in B4 doesn't print, and now I do have what I wanted. Thank you, that was helpful! Monique "Ron Rosenfeld" wrote: On Fri, 19 Dec 2008 17:33:02 -0800, Monique wrote: I have a spreadsheet that I use to calculate dates for inventory supplies. I have a formula that works great except for one function - I would like to be able to use the start date of any month (January, February, March etc.) in calculating what the next working day will be after entering 1/1/08, 2/1/08 etc. The formula I now use is =WORKDAY(C4,1,holidays) This formula does not choose the first working day correctly. January is correct when I insert 1/1/08, February also, with 2/1/08 but March 3/1/08 is on a Saturday. I would like my formula to also calculate the next start date of the month, even if I use 1/1/08 or 2/1/08 or 3/1/08 as a start date. i.e. C4: 3/1/08 =WORKDAY(U4,1,holidays) =WORKDAY(W4,1,holidays) copied to: C 40 - S 40 The month ends on the proper day. I am not sure this is possible, but I would love someone who could help me figure out how to do it. Thanks! Monique Perhaps I am not understanding your problem correctly, as I don't see how February would be correct using 2/1/08 in C4. What I understand is that, with some random date in C4, you want your formula to return the first working day of the month in C4. If that is correct, then another way of stating that is that you want to return the first working day after the last day of the month preceding the date in C4. To do that: =WORKDAY(C4-DAY(C4),1,holidays) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating monthly start date with a start date
On Sat, 20 Dec 2008 00:02:01 -0800, Monique
wrote: Hi Ron, I couldn't get this to work in the C4 cell like I wanted to but I left it in C4 and put the date I wanted in B4. I changed the font color to white so the date in B4 doesn't print, and now I do have what I wanted. Thank you, that was helpful! Monique Well, the cell that you reference cannot be in the same cell that you have the formula. Glad you got it working. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining an annual review date from an employee start date | Excel Worksheet Functions | |||
Minimum Date Greater Than Observation Start Date | Excel Discussion (Misc queries) | |||
Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Calculating effective time from start/end date+time | Excel Worksheet Functions |