Ed's Lotus Formula page contains some of his Lotus Formulas. Ed006@aol.com - construction superintendent from Dawsonville, Ga.

Welcome to Ed Ingram's Lotus Formulas Page

This Lotus formula page by Ed Ingram of Dawsonville, Ga.

Here are some formulas that produce strings that often look like numbers.

The following Lotus formula produces the current time in this format: 4:32 PM.
        @choose(@mod(@now*2,1)*12,"12"," 1"," 2"," 3"," 4"," 5"," 6"," 7"," 8"," 9","10","11")&":"&
        @if((@mod(@now*24,1)*60)>=10,(@string(@int(@mod(@now*24,1)*60),0)),"0"&@string(@int(
        @mod(@now*24,1)*60),0))& . cont.-> @if(@mod(@now,1)<0.5," AM","PM")

The following Lotus formula produces Military time in this format: 16:32.
        @choose(@mod(@now,1)*24,"00","01","02","03","04","05","06","07","08","09","10",
        "11","12","13","14","15","16","17","18","19","20","21","22","23","24")&":"&@if
        ((@mod(@now*24,1)*60)<10,"0","")&@string(@int(@mod(@now*24,1)*60),0)

The following Lotus formula produces the date / time string like 6/24/99 10:14 AM
        @if(@month(@now)<10," ","")&@string(@month(@now),0)&"/"&@if(@day(@now)
        <10,"0","")&@string(@day(@now),0)&"/"&@if(@year(@now)>99,"20","")&
        @left(@string(@year(@now),0),2)&" "&@choose(@mod(@now*2,1)*12,
        "12"," 1"," 2"," 3"," 4"," 5"," 6"," 7"," 8"," 9","10","11")&":"&@if((@mod
        (@now*21,1)*60)<10,"0","")&@string(@int(@mod(@now*24,1)*60),0)&
        @if(@mod(@now,1)<0.5," AM"," PM")

This Lotus formula puts the date / time in a sentence ... 7/04/99 and it's 9:38 AM.
        +"... "&@if(@month(@now)<10," ","")&@string(@month(@now),0)&"/"&@if(@day
        (@now)<10,"0","")&@string(@day(@now),0)&"/"&@if(@year(@now)>99,"20",
        "")&@left(@string(@year(@now),0),2)&" and it's "&@choose(@mod(@now
        *2,1)*12,"12"," 1"," 2"," 3"," 4"," 5"," 6"," 7"," 8"," 9","10","11")&":"&@if((@mod
        (@now*24,1)*60)<10,"0","")&@string(@int(@mod(@now*24,1)*60),0)
        &@if(@mod(@now,1)<0.5," AM"," PM")

This Lotus formula gives the current date in this format: July 4th, 1999.
       @choose(@month(@now),"","January","February","March","April","May","June",
        "July","August","September","October","November","December")&" "&
        @string(@day(@now),0)&@choose(@day(@now),"","st","nd","rd","th","th","th",
        "th","th","th","th","th","th","th","th","th","th","th","th","th","th","st","nd",
        "rd","th","th","th","th","th","th","th","st","nd")&", "&@if(@year(
        @now)>99,"20","19)&@right(@string(@year(@now),0),2)

This Lotus formula gives the last day of the current month in this format: 07/31/99.
@date(@year(@now),@month(@now),@choose(@month(@now),"",31,
@if(@mod(@year(@now),4)=0,29,28),31,30,31,30,31,31,30,31,30,31))

This Lotus formula gives the 15th of next month in this format: 08/15/99
@date(@if(@month(@now)=12,@year(@now)+1,@year(@now)),(@if
(@month(@now)=12,1,@month(@now)+1)),15)

Put your birthday in cell 'a1' and this Lotus formula will give your age.
@if(@mod((@now-a1-6)/365.25,1)>0.98#and#@day(@now)>=@day(a1),
@int((@now-a1+6)/365.25),@int((@now-a1-6)/365.25))

Put a date in cell 'a1' and this formula produces the date in a string.
@if(@month(a1)<10," ","")&@string(@month(a1),0)&"/"&@if(@day(a1)<10,"0","")
&@string(@day(a1),0)&"/"&@if(@year(a1)>99,"20","")&@right(@string(@year(a1),0),2)

Put a date in cell 'a1' and this Lotus formula produces a date like June 14th, 1999.
        @choose(@month(a1),"","January","February","March","April","May","June",
        "July","August","September","October","November","December")&" "&
        @string(@day(a1),0)&@choose(@day(a1),"","st","nd","rd","th","th","th",
        "th","th","th","th","th","th","th","th","th","th","th","th","th","th","st","nd","rd","th","th","th","th",
        "th","th","th","st","nd")&", "&@if(@year(a1)>99,"20","19")&@right(@string(@year(a1),0),2)

Put a date in cell 'a1' and this Lotus formula tells how many days are in that month.
@choose(@month(a1),0,31,@if(@month(a1)=2#and#@mod(@year(a1),4)
=0,29,28),31,30,31,30,31,31,30,31,30,31)

Put a date in cell 'a1' and this Lotus formula tells the no. of Tuesdays in the month.
        @if(@mod(@date(@year(a1),@month(a1),1)-1,7)=0#and#@choose(@month
        (a1),0,31,@if(@month(a1)=2#and#@mod(@year(a1),4)=0,29,28),31,30,31
        ,30,31,31,30,31,30,31)=31,1,0)+@if(@mod(@date(@year(a1),@month(a1),1)-1,7)=
        1#and#@choose(@month(a1),0,31,@if(@month(a1)=2#and#@mod(@
        year(a1),4)=0,29,28),31,30,31,30,31,31,30,31,30,31)>=30,1,0)+@if(@mod(@date
        (@year(a1),@month(a1),1)-1,7)=2#and#@choose(@month(a1),0,31,@if(@month(a1)
        =2#and#@mod(@year(a1),4)=0,29,28),31,30,31,30,31,31,30,31,30,31)>=29,1,0)+4

Put a date in cell 'a1' and this Lotus formula gives the day of the Week.
@choose(@mod(@date(@year($A$21),@month($A$21),@day($A$21))-1,7),
"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday", . cont.-> "Saturday")

Put a date in cell 'a1' and this Lotus formula gives the last day of the month.
@date(@year(a1),@month(a1),@choose(@month(a1),"",31,@if(@mod
(@year(a1),4)=0,29,28),31,30,31,30,31,31,30,31,30,31))

Put a date in cell 'a1' and this Lotus formula gives the first day of next month.
@date(@if(@month(a1)=12,@year(a1)+1,@year(a1)),@if(@month(a1)=12,1,1+@month(a1)),1)

Put a date in cell 'a1' and this Lotus formula gives the 15th of next month.
@date(@if(@month(a1)=12,@year(a1)+1,@year(a1)),(@if(@month(a1)=12,1,@month(a1)+1)),15)

Put a decimal number in cell 'a1' and this Lotus formula gives feet and inches. (Name a cell 'quo' and put " in it.)
        @string(@int((a1+0.002)),0)&"'-"&@string(@int(((a1+0.002)-@int((a1+0.002 . cont.-> )))*12),0)
        &" "&@choose(@int(16*(((+(a1+0.002)-@int((a1+0.002)))*12)-(@int . cont.-> ((((a1+0.002)
        -@int((a1+0.002)))*12))))),"","1/16","1/8","3/16","1/4","5/16","3/8","7/16","1/2","9/16","5/8",
        "11/16","3/4","13/16","7/8","15/16")&$quo

This formula entered into a single cell will produce the column letter/s each time the cell is recalculated.
        @choose((@cellpointer("col")-1)/26,"","a","b","c","d","e","f","g","h","i")&
        @choose(@mod(@cellpointer("col"),26),"z","a","b","c","d","e","f",
        "g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z")

Put a number between 1 and 512 in a Lotus worksheet - cell 'a1' and this formula
will produce the column letter/s. Examples: 5 = 'e' or 27 = 'aa'.
@choose((+a1-1)/26,"","a","b","c","d","e","f","g","h","i")&@choose(@mod(+a1,26),"z","a","b","c"
,"d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z")

Put a number less than $1 million in cell 'a1' of a Lotus spreadsheet, and this formula will produce
a string that looks like the number when recalculated. The Format used here is comma 2.
@if(a1<1000000," ",@string(@int(a1/1000000),0)&",")&@if(a1<1000," ",
@right(@string(a1/1000,0),3)&",")&@right(@string(a1,2),6)

This formula adds words to the string that looks like the number in cell 'a1'.
+"Pay $"&@if(a1<1000000," ",@string(@int(a1/1000000),0)&",")&@if(a1<1000," ",
@right(@string(a1/1000,0),3)&",")&@right(@string(a1,2),6)&" to them."

Put an amount in cell 'a1' of a Lotus worksheet and put the formulas below in cells a2' through 'a15'
and the amount in cell 'a1' will be written out in words like one would use on a check in cell 'a15'.
Cell A1 = Enter an amount.
Cell A2 = @if(a1>=1000,@int(a1/1000),0)
Cell A3 = @if(a1<100000," ","")&@if(a1<10000," ","")&@if(a1<1000," ","")&
                @if(a1< . cont.-> 100," ","")&@if(a1<10," ","")&" "
Cell A4 = +"***"&@trim(a3&@if(@int(a1/1000)>0,@string(@int(a1/1000),0)&",","")
                &@if(@int(a1/1000)>0#and#a1-@int(a1/1000)*1000<100,"0","")
                &@if(@int(a1/1000 . cont.-> )>0#and#a1-@int(a1/1000)*1000<10,"0","")
                &@string(a1-@int(a1/1000)*1000, . cont.-> 2)&"")
Cell A5 = @if(a1>=100000,@choose(@int(a1/100000),"","One Hundred ","Two Hundred ",
                 "Three Hundred ","Four Hundred ","Five Hundred ","Six Hundred ","Seven Hundred ",
                 "Eight Hundred ","Nine Hundred "),"")
Cell A6 = @if((a2-@int(a2/100)*100)>0#and#(a2-@int(a2/100)*100)<=10,@choose((a2-
                @int(a2/100)*100),"No","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten"),"")
Cell A7 = @if(a2-@int(a2/100)*100)>10#and#(a2-@int(a2/100)*100)<20,@choose((a2-
                @int(a2/100)*100)-11,"Eleven","Twelve","Thirteen","Fourteen","Fifteen",
                "Sixteen","Seventeen","Eighteen","Nineteen"),"")
Cell A8 = @if((a2-@int(a2/100)*100)>=20,@choose((a2-@int(a2/100)*100)/10,"","",
                "Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"),"")
Cell A9 = @if(a2-@int(a2/100)*100>=20,@if(a2-@int(a2/10)*10>=1,@choose(a2-@int(a2/10)*
                10,"","-One","-Two","-Three","-Four","-Five","-Six","-Seven","-Eight", . cont.-> "-Nine"),""),"")
Cell A10 = @if(a2>0," Thousand ","")
Cell A11 = @if((a1-@int(a1/1000)*1000)/100>=1,@choose((a1-@int(a1/1000)*1000)/100,"",
                 "One Hundred ","Two Hundred ","Three Hundred ","Four Hundred ","Five Hundred ",
                 "Six Hundred ","Seven Hundred ","Eight Hundred ", . cont.-> "Nine Hundred ",""),"")
Cell A12 = @if($a$1-@int($a$1/100)*100<20,@choose($a$1-@int($a$1/100)*100,"","One",
                 "Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen",
                 "Fourteen","Fifteen","Sixteen","Seventeen","Eighteen", . cont.-> "Nineteen"),"")
Cell A13 = @choose(($a$1-@int($a$1/100)*100)/10,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")
Cell A14 = @if(a1-@int(a1/100)*100>=20,@if(a1-@int(a1/10)*10>=1,@choose(a1-@int(a1/10)*10,"","-One",
                 "-Two","-Three","-Four","-Five","-Six","-Seven","-Eight", . cont.-> "-Nine"),""),"")
Cell A15 = +"*** "&a5&a6&a7&a8&a9&a10&a11&a12&a13&a14&@if($a$1<1,"No","")&" and"
                 &@if(@mod($a$1,1)>0,@right(@string($a$1,2),2),"No")&"/100 Dollars ***" --

Click to e-mail: Ed006@aol.com

Our Main Index Page contains links to all of our web pages.