Formula to check if date/time is between to specified times and return a specified value

I am looking for a formula that can check if date/time is between to a start date/time cell and end date/tome cell and return a specified value.

For instance:

If cell F1 has a enter value of 1/1/2018 12:00:00 AM; and cell H1 has and 1/1/2018 12:59:59 AM, then I want the value return to be "1".
or
If cell F1 has a enter value of 1/1/2018 1:00:00 AM; and cell H1 has and 1/1/2018 1:59:59 AM, then I want the value return to be "2".
So on and so forth; All of the way up to return value of 24

or
If cell F1 has a enter value of 1/1/2018 12:00:00 AM; and cell H1 has and 1/1/2018 1:59:59 AM, then I want the value return to be "1/2" oor whatever return format is easiest..

The Dates are included in the cell, but I just need to determine if the times in the two cells fall between two times and return the appropriate value.

Any help will be appreciated. Thanks in advance.

Tim

Your example doesn't make sense to me
the first 2 lines are effectively the same in time difference
what is your logic for the numbers?

can you just explain in english, especially what is the appropriate value

This is a Table that is set up for the user to add a new Row using a Macro.

Step 1
When a new Row is created and Cell C1 is populated with any data, both Cells E1 and F1 are time stamped with the formula =IF(C1<>"",IF(E1="",NOW(),E1),"") and =IF(C1<>"",IF(F1="",NOW(),F1),"")

Cell E1 is formatted to show the date. m/d/yyyy.

Cell F1 is formatted to show the time. h:mm:ss AM/PM

This is the Time Stamp for the data input in C1.

Step 2
When Cell D1 is populated with any data, Cells H1 is time stamped with the formula =IF(D1<>"",IF(D1="",NOW(),D1),"")
Cell E1 is formatted to show the date. h:mm:ss AM/PM

Step 3
If Cell H1 is Time Stamped, then the total time between is calculated in cell I1 with the formula =IF(H61="","",(H61-F61))
Cell I1 in hh:mm:ss, and give me the total time between the Data Inputted in C1 and D1.

Step 4
I would like to know what hours of the day that the cells C1 & D1 had data imputed into them and return a value based 24 hours
Example goes on to 24 hours.
hh:mm:ss
0:00:00 0:59:59 Cell J1 = 1
1:00:00 1:59:59 Cell J1 = 2
2:00:00 2:59:59 Cell J1 = 4
3:00:00 3:59:59 Cell J1 = 5
4:00:00 4:59:59 Cell J1 = 6

Example:
So if Data in entered into C1 and time stamped in cell F1 at 0:00:01 AM
and the Data in D2 is time stamped in cell at 0:59:59 AM
I both time stamps in F1 and H1 are between 0:00:00 - 0:59:59. I would like the data in Cell J1 to give a value of "1".

or

if Data is entered into C1 and time stamped in cell F1 at 0:00:01 AM
and the Data in D2 is time stamped at 1:59:59 AM
I would like the data in Cell J1 to give a value of "1,2".

or

if Data is entered into C1 and time stamped in cell F1 at 2:00:01 AM
and the Data in D2 is time stamped at 4:59:59 AM
I would like the data in Cell J1 to give a value of "4,5,6".

What am I trying to achieve? I want to know when a new Row is created and the user enter data into C1 and then D1.
It takes too much time to look at hundred of time stamps to figure out When or what hours of the days is data C1 and D1 the most.

Maybe there is a better convention of easier way to do it?


Thanks
Tim

It is clearer what you want, formating doesn't change the underlying value so for example c1 and d1 will contain the same value all be it is displayed differently, also the now function is volatile so will changed for every cell when your spreadsheet calculates, unless you have changed it into values

hour(d1) will give you the hour although it is 0-23 if you cant live with this hour(d1)+1

doing a start and an end hour is straightforward making 4,5,6 is much harder than 4,6 and actually should it be 3, 4, 5?

without an attachment or sight of your current code it is hard to be more specific

pivot tables and countif functions would summarize your data from the timestamps

https://www.extendoffice.com/documen...p-by-hour.html