Date & Time Functions

[Mr Roboto]

I'm creating a spreadsheet that will connect to a database and extract data based on date time etc. I have to have a start date and time and an end date and time. The end date and time is easy as I can use the =NOW() function which will give you the current date and time, this also tells me when the report was run.
The start date and time is the problem. There will be two files set up one for days and one for nights. I want to be able to run the day shift spreadsheet at any time during the day but have it automatically have a start date of today and a start time of 6:50. The night shift report will be run the next day so the start date and time will be yesterday at 18:50.
I want to have the spreadsheet automatically put this data in the start and end cells when the file is opened. The date and time have to be in the same cell as it is when you use the NOW function
Any suggestions.

 

[Gates Is Antichrist]

=TEXT(NOW(),"mm/dd/yy") & TEXT(TIME(6,50,0)," hh:mm")
or, seriously, folks,
=TEXT(NOW(),"mm/dd/yy") & " 06:50"
and
=TEXT(NOW()-1,"mm/dd/yy") & " 18:50"

You didn't mention if you had to run a night report Monday morning on Friday data. If so, I'm sure you'll survive

 

[Mr Roboto]

It's easy when someone shows you.
Many thanks.

 

[Gates Is Antichrist]

Thank you for the courtesy. The "thank you" rate for successful solutions feels like about 2%. (Incorrect solutions kick the response rate up to at least 97% )