Date and IIF
 

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

Date and IIF
 

Maybe...

Dim A as date
Dim B as date

'how does A get populated???
'test data
a = dateserial(2008,1,2) + timeserial(12,30,45)

if day(a) < 11 then
b = dateserial(year(a), month(a), 1)
else
b = dateserial(year(a), month(a) + 1, 1)
end if

or as one line:
B = DateSerial(Year(A), Month(A) - CBool(Day(A) 10), 1)

What's N?




scubadiver wrote:

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

--

Dave Peterson

Date and IIF
 

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis
--
Kevin Backmann


"scubadiver" wrote:


I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

Date and IIF
 

I *was* looking for the Access forum, d'oh!

"Kevin B" wrote:

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis
--
Kevin Backmann


"scubadiver" wrote:


I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

Date and IIF
 

Please follow following steps

At first format the cell in column B to display date and time formate as:-
Select a cell in B(say B1)right clickformat cellsselect number tabselect
date from category listselect 3/14/01 13:30 from type list.
Similarly convert cells in column A in date time format too.
Then,
1.Select a cell in column B (say B1)
2.Enter following formula
=IF(DAY(A1)<11,DATE(YEAR(A1),MONTH(A1),1),IF(DAY(A 1)=11,DATE(YEAR(A1),MONTH
(A1)+1,1)))
3.Similarly apply for other required cells in column B

Have a nice time€¦


Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

--
Message posted via http://www.officekb.com

Date and IIF
 

Thanks for that Kevin. It looks like it has worked.

Cheers!

"Kevin B" wrote:

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis
--
Kevin Backmann


"scubadiver" wrote:


I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!