Formula for next day based on clock
 

:confused: My worksheet contains a formula to change the date in A2 to
Text in B2 indicating the day of the week. I need a formula in C2 that
would take the date in cell A2 and take into consideration the time in
cell D2. My company's day starts at 06:00 and runs until 06:00 the
next day. I want C2 to show the next day after midnight.

Example: B2 would show Tues for the date 5/31/2005, but after midnight
(in D2), I want C2 to show Wed.


+-------------------------------------------------------------------+
|Filename: DayNumber formula.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3446 |
+-------------------------------------------------------------------+

--
dcoates
------------------------------------------------------------------------
dcoates's Profile: http://www.excelforum.com/member.php...fo&userid=2320
View this thread: http://www.excelforum.com/showthread...hreadid=375616

I think the following formula will show the current day of the week and it
automatically changes with time:

=INDEX({"Monday","Tuesday","Wednesday","Thursday", "Friday","Saturday","Sunday"},WEEKDAY(NOW(),2) )
Today is June 1, 2005 then the result will be Wednesday. After tonight
midnight, the result will be Thursday....

Hope this is what you want.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist

www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====


================================================== ====================
"dcoates" wrote in
message ...

:confused: My worksheet contains a formula to change the date in A2 to
Text in B2 indicating the day of the week. I need a formula in C2 that
would take the date in cell A2 and take into consideration the time in
cell D2. My company's day starts at 06:00 and runs until 06:00 the
next day. I want C2 to show the next day after midnight.

Example: B2 would show Tues for the date 5/31/2005, but after midnight
(in D2), I want C2 to show Wed.


+-------------------------------------------------------------------+
|Filename: DayNumber formula.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3446 |
+-------------------------------------------------------------------+

--
dcoates
------------------------------------------------------------------------
dcoates's Profile:
http://www.excelforum.com/member.php...fo&userid=2320
View this thread: http://www.excelforum.com/showthread...hreadid=375616

Thanks Daniel,

Your formula works if I am making the assumption that the date I am
working with is today's day. However, I am working with static dates
of events that have happened and it may be that an incident occurred on
4/28/05 @01:30 am. I want B2 to show that the incident happened on Thur
(4/28/05 by company clock), that in reality it occurred after midnight
so it would read Fri in C2 (still showing 4/28/05 in A2).


--
dcoates
------------------------------------------------------------------------
dcoates's Profile: http://www.excelforum.com/member.php...fo&userid=2320
View this thread: http://www.excelforum.com/showthread...hreadid=375616