Formula's with dates

[PGD15]

Hi,

I am awful with formula's that relate to dates, this should be an easy request.

I want to say if cell a (example) is equal to the month in cell b (this is in date format so 01/06/2018, for june) then display the date six months ago (01/01/2018).

thanks in advance

 

[njimack]

Does column A contain a date, or just the month number? And what result do you want if they're not the same month?

 

[PGD15]

Does column A contain a date, or just the month number? And what result do you want if they're not the same month?

I think I worded this badly... let me re-try

I want to say if cell A1 (contains date, shown as june-18 = 01/06/2018) then i want the value in cell b1 to be equal to the date six months ago.

so a1 - June-18
i want b1 to display Jan-18

if the cell data in a1 changes i want b1 to automatically change too.

is the clearer?

 

[Peter_SSs]

so a1 - June-18
i want b1 to display Jan-18

is the clearer?

Not really, as 6 months before June is December in the previous year.

In any case, try
=EDATE(A1,-6)
If you really want June to become January then change the 6 to 5.

 

[njimack]

Either of the below should work for you.

Excel Workbook
	A	B
1	01/06/2018	01/01/2018
2	01/06/2018	01/01/2018
3	01/06/2018	01/01/2018
Sheet1

 

[PGD15]

Either of the below should work for you.
Sheet1

	A	B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]01/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]01/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]01/01/2018[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =EDATE(A1,-5) B2 =EOMONTH(A2,-6)+1 B3 =DATE(YEAR(A3),MONTH(A3)-5,1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks both,

Once again Peter don't question my maths I wanted 6 months but that was irrelevant to the dates I gave, just an example so I can adapt to work.

Thanks!

 

[Peter_SSs]

Once again Peter don't question my maths

I won't - IF it makes sense.