Thread: Formulas needed for Calendar?

I am trying to convert a calendar that I use in Lotus 123 to Excel.

It will not automatically convert due to the formulas.

I have never used Excel before and need to some help with converting
the formulas to whatever is compatiable with Excel.

I do not want to use a pre existing calendar as it will not work with
my project.

Would appreciate any help with this. Here are the formulas that I am
using with 123 that need converting to Excel.

Formula 1
@DATEINFO(@DATE($L$4,A8,1),6)&" "&@STRING(A7,0)

This puts the Month and Year from a Date that I enter into a cell.

Formula 2

@DATEINFO(@DATE($A7,$A8,D8),1)

This puts a week day name in a Field (this inserts SUN) then I use it
to put the rest of the days in the other 6 cells. I just change the 1
to a 2 and so on.

Formula 3

@IF(@DATEINFO(@DATE($A7,$A8,1),3)=M$1,1,"")

This puts the day number in the calendar. I just copy to the right to
get the rest of the day numbers for each week.

this will give month number and year number from a date in a1
7 2005
=MONTH(A1)&" "&YEAR(A1)

--
Don Guillett
SalesAid Software
donaldb@281.com
"Randy" <ranmcc@msn.com> wrote in message
news:1120656624.882439.323980@g14g2000cwa.googlegroups.com...
> I am trying to convert a calendar that I use in Lotus 123 to Excel.
>
> It will not automatically convert due to the formulas.
>
> I have never used Excel before and need to some help with converting
> the formulas to whatever is compatiable with Excel.
>
> I do not want to use a pre existing calendar as it will not work with
> my project.
>
> Would appreciate any help with this. Here are the formulas that I am
> using with 123 that need converting to Excel.
>
> Formula 1
> @DATEINFO(@DATE($L$4,A8,1),6)&" "&@STRING(A7,0)
>
> This puts the Month and Year from a Date that I enter into a cell.
>
> Formula 2
>
> @DATEINFO(@DATE($A7,$A8,D8),1)
>
> This puts a week day name in a Field (this inserts SUN) then I use it
> to put the rest of the days in the other 6 cells. I just change the 1
> to a 2 and so on.
>
> Formula 3
>
> @IF(@DATEINFO(@DATE($A7,$A8,1),3)=M$1,1,"")
>
> This puts the day number in the calendar. I just copy to the right to
> get the rest of the day numbers for each week.
>

> Formula 1
> @DATEINFO(@DATE($L$4,A8,1),6)&" "&@STRING(A7,0)
>
> This puts the Month and Year from a Date that I enter into a cell.
>
Use the MONTH and YEAR formulas to get the Month and Year and concatenate
them using a &

> Formula 2
>
> @DATEINFO(@DATE($A7,$A8,D8),1)
>
> This puts a week day name in a Field (this inserts SUN) then I use it
> to put the rest of the days in the other 6 cells. I just change the 1
> to a 2 and so on.

WEEKDAY returns a number from 1-7 identifying the day of the week.
>
> Formula 3
>
> @IF(@DATEINFO(@DATE($A7,$A8,1),3)=M$1,1,"")
>
> This puts the day number in the calendar. I just copy to the right to
> get the rest of the day numbers for each week.
>
>
I'm not sure what you mean by DAY number. If the Day number for today is 6,
use
DAY()

"Randy" wrote:

> I am trying to convert a calendar that I use in Lotus 123 to Excel.
>
> It will not automatically convert due to the formulas.
>
> I have never used Excel before and need to some help with converting
> the formulas to whatever is compatiable with Excel.
>
> I do not want to use a pre existing calendar as it will not work with
> my project.
>
> Would appreciate any help with this. Here are the formulas that I am
> using with 123 that need converting to Excel.
>
> Formula 1
> @DATEINFO(@DATE($L$4,A8,1),6)&" "&@STRING(A7,0)
>
> This puts the Month and Year from a Date that I enter into a cell.
>
> Formula 2
>
> @DATEINFO(@DATE($A7,$A8,D8),1)
>
> This puts a week day name in a Field (this inserts SUN) then I use it
> to put the rest of the days in the other 6 cells. I just change the 1
> to a 2 and so on.
>
> Formula 3
>
> @IF(@DATEINFO(@DATE($A7,$A8,1),3)=M$1,1,"")
>
> This puts the day number in the calendar. I just copy to the right to
> get the rest of the day numbers for each week.
>
>

a re-read of this suggests that custom formatting might do what you desire?

--
Don Guillett
SalesAid Software
donaldb@281.com
"Don Guillett" <donaldb@281.com> wrote in message
news:eQ%23MjkjgFHA.2632@TK2MSFTNGP09.phx.gbl...
> this will give month number and year number from a date in a1
> 7 2005
> =MONTH(A1)&" "&YEAR(A1)
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Randy" <ranmcc@msn.com> wrote in message
> news:1120656624.882439.323980@g14g2000cwa.googlegroups.com...
> > I am trying to convert a calendar that I use in Lotus 123 to Excel.
> >
> > It will not automatically convert due to the formulas.
> >
> > I have never used Excel before and need to some help with converting
> > the formulas to whatever is compatiable with Excel.
> >
> > I do not want to use a pre existing calendar as it will not work with
> > my project.
> >
> > Would appreciate any help with this. Here are the formulas that I am
> > using with 123 that need converting to Excel.
> >
> > Formula 1
> > @DATEINFO(@DATE($L$4,A8,1),6)&" "&@STRING(A7,0)
> >
> > This puts the Month and Year from a Date that I enter into a cell.
> >
> > Formula 2
> >
> > @DATEINFO(@DATE($A7,$A8,D8),1)
> >
> > This puts a week day name in a Field (this inserts SUN) then I use it
> > to put the rest of the days in the other 6 cells. I just change the 1
> > to a 2 and so on.
> >
> > Formula 3
> >
> > @IF(@DATEINFO(@DATE($A7,$A8,1),3)=M$1,1,"")
> >
> > This puts the day number in the calendar. I just copy to the right to
> > get the rest of the day numbers for each week.
> >
>
>

Thanks for the suggestions, but I got an example from John Walkenback
using his older array calendar that works great.

It gave me exactly what I needed to develop a calendar in xls.