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
Last edited by Rajesh Kr Joshi; 08-03-2013 at 01:56 PM.
Hi
If I understood correctly, in D3
=B1
In D4 and copied down,
=INT(D3)+C4
Cheers !
Excel Range to BBCode Table
Use Social Networking Tools If You Like the Answers !
Message to Cross Posters
@ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
See attached
Hi Experts,
Thanks for the quick solution. Working perfect
Thanks
Rajesh
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)
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Posting Permissions
Reply With Quote
Bookmarks