Show Overnight Data as Occuring on One Day

[bjk896]

I have data from an outposted data logger that logs date and time of an occurrence eg 2021-09-28_19-15-07 for Date and hh-mm-ss. This data only occurs between 1700 and 0700 next day, so for one night’s effort I get Date 1 for occurrences to midnight and Date 2 for those post midnight.

2021-09-28_19-15-07 shows for 28/9/2021 and
2021-09-29_03-37-16 shows for 29/2/2021

I don’t care which day is used when it is all finally put on a scatter chart but it is more meaningful for this to be shown as 1 day.
I have no problem converting formats between time and date serial and numbers

How do I do it ?

I then make a scatter chart using date serials as numbers and decimal times (with X = Date, Y = Time) How can I eliminate the redundant times between 0700 and 1700 in the Time axis?

bjk

 

[bjk896]

bebo02199 Been offline for a while - I'm not familiar with the ----(SUBSTITUTE(RIGHT($A$14,8) ---- component of your formulae for B Column

Could you fill me in a bit please bjk

Well, I understand what the over all formula does but not what the "$A$14,8" part is doing
bjk