Date Check/Combining Dates

[kirpal singh manral]

Hi ,

I have two columns(Date1 & Date2) with datatype as datetime .

Desired Output columns are DesiredDate from the Date1 and Date2.

One day is from6am 4th Feb to 5:59am 5th Feb(Desired Date: 4-02-2021).

Attached is screenshot.

[Varun Khandelwal]

One way to do this can be:

1. Create calulate column with data and time in it:Date([TimeStamp]) & Time([TimeStamp])

2. Create another column Shift: in that use if and time column created earlier to assign Day and Night shift.

You could do 1&2 together in one step but i have put it in 2 just to explain things easily.

[kirpal singh manral]

TIA

[kirpal singh manral]

Thanks for your response.

 

But i have two dates to compare to get the desired date.

 

Assuming Date1 as startdate and Date2 as enddate.

 

Date1 : 4/02/2021 02:20:20 AM

 

Date2: 4/02/2021 02:35:02 AM

 

I want a new date which should come out as 3/02/2021.

 

One day is from 6am 3rd Feb to 5:59am 4th Feb(Desired Date: 3-02-2021).

 

I want to use this date for filtering.

[Varun Khandelwal]

Ok. You can add another calculated column which is something like this:

If(Time([Date1])

[Fabian Duerr]

I think this should do the job for you:

case

when Hour(Time([DTTM]))>=6 then Date([DTTM])

else Date(DateAdd("day",-1,[DTTM]))

endPlease replace the [DTTM] column with your desired column.

[kirpal singh manral]

Thanks