How To Change Date Based On Time Using Formula

[Rajesh Kr Joshi]

Hi,

Looking for a formula to change date automatically based on time in previous column.
example:

I have week start date in Cell B1 and Time Starts from Cell C3,C4.. i.e. 12:00 PM, 13:00PM ......23:00, 00:00. I want to change the date in column D starting cell D3which will changes at 00:00 hrs. the input for first date will be B1. I am unable to upload the sample file, Below is the sample output

Time Date
20:00 5-Aug
21:00 5-Aug
22:00 5-Aug
23:00 5-Aug
0:00 6-Aug
1:00 6-Aug


Thanks
Rajesh

[Admin]

Hi

If I understood correctly, in D3

=B1

In D4 and copied down,

=INT(D3)+C4

[alansidman]

See attached

[Rajesh Kr Joshi]

Hi Experts,

Thanks for the quick solution. Working perfect :)

Thanks
Rajesh

[Excel Fox]

in addition to alansidman's solution, even if it is an hourly change and it's a 'half past' hour, for example, 11:30 PM, 00:30PM, 1:30PM, where there's no zero hours (sharp midnight), then this formula will work

=IF(C4<C3,D3+1,D3)