Hiding dates if they are before a certain date

[chris_bosten]

Hi,

I hope you can help

I have the following sheet "jobs due"

Job ID	Job Last Completed	Job Due Date
MJID0001	15/01/2025	15/07/2025
MJID0002
MJID0003		31/12/1900
MJID0004	16/01/2025	16/02/2025
MJID0005		31/03/1900
MJID0006		31/12/1900
MJID0007		31/12/1901
MJID0008		31/01/1900

Job last completed is using a formula to look up the most recent date an event occured:
=MAX(IF('Jobs Completed'!$C$2:$C$10000='Jobs Due'!A3,'Jobs Completed'!$A$2:$A$10000)) - it is getting this information from a separate sheet "jobs completed"

Job Date	Day of Week	Job ID / Route Number	Category	Technician	WO Number
15/01/2025	Wednesday	MJID0001	RTC-I	Alex	WO456720001
16/01/2025	Thursday	MJID0004	RTC-I	Alex	WO456730004
17/01/2024	Friday	MJID0012	RTF	Colm	WO453080012
15/01/2025	Monday	MJID0021	RTT	Alex	WO456720021
15/01/2024	Monday	Route 1	RTC-I	Alex	WO453061
21/01/2025	Tuesday	Route 1	RTC-I	Alex	WO456781
17/01/2025	Friday	MJID0012	RTF	Colm	WO456740012

I am using a custom date format dd/mm/yyyy;; to hide any dates with 0 value (01/01/1900)

In the jobs due sheet I want to add months to the date that can be found in column M of the jobs due sheet but only if the date in column B is after 1/1/25 (or not 00/01/1900 / blank)

Column M of the jobs due sheet:

Job Frequency (months)
6
0
12
1
3
12
24
1

The formula I am trying is: =IF(B3="00/01/1900","",DATE(YEAR(B3),MONTH(B3)+M3,DAY(B3)))

But this adds months to each row, even if it's 00/01/1900 - it adds only if the value in column M is > 0

Job ID	Job Last Completed	Job Due Date	Job Due Time Period	Route	Route Last Completed	Route Due Date	Route Due Time Period	Routes	Job Frequency (months)
MJID0001	15/01/2025	15/07/2025	0	6
MJID0002	0	0
MJID0003	31/12/1900	0	12
MJID0004	16/01/2025	16/02/2025	Route 1	21/01/2025	Route 1	1
MJID0005	31/03/1900	0	3
MJID0006	31/12/1900	0	12
MJID0007	31/12/1901	0	24

Basically, I only want a job due date to populate if I can see a date in column B.

Can you help?

Best wishes
Chris

 

[coulombevin]

Hi Chris,

Have you tried to change = by <> like so:

=IF(B3<>0,"",DATE(YEAR(B3),MONTH(B3)+M3,DAY(B3)))

or >

=IF(B3>0,"",DATE(YEAR(B3),MONTH(B3)+M3,DAY(B3)))

bests regards,

Vincent

 

[chris_bosten]

Sadly it didn't work!

Same if I use <>

..hmmm

 

[Alex Blakenburg]

That logic looks to be back to front to me. Try:

=IF(B3=0,"",DATE(YEAR(B3),MONTH(B3)+M3,DAY(B3)))

 

[chris_bosten]

It's ok, I've worked out a workaround!

I tried the above with the original formula... but it's ok. I created a boolean logic to say if a date was after 1/1/25, then I created a formula to return values only if the boolean logic was true, and that worked.