How can i find my 1st of the month??

[richard hales]

Hi All
am working on a time sheet project. i'm now working on the monthly holiday planner.
Am looking for a way for a formlua to look at 2 rows and return the first of the month i.e 01-01-2009
The rows which need to be looked at are the first two rows of any given month because the 1st of the month will always be in the first two weeks of a month.
my rows are
1st week to look in. b4-h5
2nd week to look in. b19-h19
Now b4 as a paste link in it ..=roster!$H$2 which is the start date of the roster
the result of the search is then put into b2 as Month only ie. January

 

[sous2817]

Not to worry...

For versions earlier than 2007:

1) On the Tools menu, click Add-ins.
2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK.

You may need to restart excel.

For 2007:

1) Click the windows logo at the top left, then "Excel Options"
2) Select the "Add-Ins" option on the left side
3) At the bottom of the menu you'll see "Mange: Excel Add-ins", press the "Go" button
4) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK.

Here is a link that may help: http://office.microsoft.com/en-us/excel/HP011277241033.aspx

 

[richard hales]

just tried to do the add it and it came back with cant do and a error1706 code think i need to get hold the disc for 2003
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 314.25pt; HEIGHT: 168pt" type="#_x0000_t75"><v:imagedata o:title="" src="file:///C:\Users\Sphinx\AppData\Local\Temp\msohtml1\01\clip_image001.png"></v:imagedata></v:shape>

 

[richard hales]

many thanks just tried to do it but it came back with an error1706 think i need to get the 2003 disc to complete that

 

[sous2817]

Analyisis tool pack wasn't on the list? I'm sure there's a way to do it wthout it. Maybe one of the formula gurus will take a look at this and solve it for you.

 

[richard hales]

am now trying lookup,index match formluas but not get far as yet but many thanks to all the help.

 

[sous2817]

Hello Richard,

Try the below in B2:

=IF(DAY(B4)>17,DATE(YEAR(B4),MONTH(B4)+1,1)+1,DATE(YEAR(B4),MONTH(B4)+1,1)-1)


And format B2 as custom and use 'mmmm' (without the quotes).

 

[richard hales]

Sous2817
you've just become a guru. i just dropped one thing and it finds my 1st of the month in planner
your formlua
=IF(DAY(B4)>17,DATE(YEAR(B4),MONTH(B4)+1,1)+1,DATE(YEAR(B4),MONTH(B4)+1,1)-1)
my change
=IF(DAY(B4)>17,DATE(YEAR(B4),MONTH(B4)+1,1),DATE(YEAR(B4),MONTH(B4)+1,1)-1)
Many Many thanks

 

[sous2817]

Wonderful, glad it all worked out! Post back if you have any other issues. Good luck with the rest of your project.

 

[richard hales]

sous2817
i thank for the help and may take you up on the offer. i have one but am going to see if i can work it out first its a challenge my by my boss its to do with hyperlinks, link from one cell to other and when that cell (the 2nd one) as been completed it returns you back to the first cell sounds confusing i know and find the right word to post it will be a job. Once again i thank you so and wish you the best.