If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time Format to Text Output - A Tough One !
Hi
I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 11:20:00 PM to 7:19:00 AM should display the word NIGHT Assume the original time is in cell B8 Thanks John |
#2
|
|||
|
|||
Time Format to Text Output - A Tough One !
Hi
Further to my previous post I have tried the following formula. It almost works, it displays the Day and the Aft ok but where the Night should be shows only a blank cell. =IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")="07:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"15:20:00"),"Day",IF(AND(TEXT( B8-INT(B8),"hh:mm:ss")="15:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"23:20:00"),"Aft",IF(AND(TEXT( B8-INT(B8),"hh:mm:ss")="23:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"07:20:00"),"Night",""))) I hope this helps Thanks John "John Calder" wrote: Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 11:20:00 PM to 7:19:00 AM should display the word NIGHT Assume the original time is in cell B8 Thanks John |
#3
|
|||
|
|||
Time Format to Text Output - A Tough One !
"John Calder" wrote:
I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") How is this different from the thread you started (and I thought I finished ;-) on 8/17/2009 at 5:37 PM entitled "Time Formula"? See http://www.google.com/url?url=http:/...aM 1oH3dlXmhA . Was there something with the very different and more compact solution that I offered? ----- original message ----- "John Calder" wrote in message ... Hi Further to my previous post I have tried the following formula. It almost works, it displays the Day and the Aft ok but where the Night should be shows only a blank cell. =IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")="07:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"15:20:00"),"Day",IF(AND(TEXT( B8-INT(B8),"hh:mm:ss")="15:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"23:20:00"),"Aft",IF(AND(TEXT( B8-INT(B8),"hh:mm:ss")="23:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"07:20:00"),"Night",""))) I hope this helps Thanks John "John Calder" wrote: Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 11:20:00 PM to 7:19:00 AM should display the word NIGHT Assume the original time is in cell B8 Thanks John |
#4
|
|||
|
|||
Time Format to Text Output - A Tough One !
Try this.
=IF(A1="","",IF(AND(--TEXT(A1,"HH:MM:SS")=TIME(7,20,0),(--TEXT(A1,"HH:MM:SS")=TIME(15,19,59))),"DAY",IF(AND (--TEXT(A1,"HH:MM:SS")=TIME(15,20,0),(--TEXT(A1,"HH:MM:SS")=TIME(23,19,59))),"AFT","NIGHT "))) change the cell reference A1 to your desired cell. If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "John Calder" wrote: Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 11:20:00 PM to 7:19:00 AM should display the word NIGHT Assume the original time is in cell B8 Thanks John |
#5
|
|||
|
|||
Time Format to Text Output - A Tough One !
Joe
Thanks for your repsonse. The difference is that the earlier post was for a formula that looked at a 2 X 12 hr shift operation and the one I need now is for a 3 X 8 hr operation. I hadnt worked out how to ammend the earlier one to suit the later one so I posted it again with the new criteria. As a result from this groups help I now have it working. Thanks you very much, it was much appreciated. John "JoeU2004" wrote: "John Calder" wrote: I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") How is this different from the thread you started (and I thought I finished ;-) on 8/17/2009 at 5:37 PM entitled "Time Formula"? See http://www.google.com/url?url=http:/...aM 1oH3dlXmhA . Was there something with the very different and more compact solution that I offered? ----- original message ----- "John Calder" wrote in message ... Hi Further to my previous post I have tried the following formula. It almost works, it displays the Day and the Aft ok but where the Night should be shows only a blank cell. =IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")="07:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"15:20:00"),"Day",IF(AND(TEXT( B8-INT(B8),"hh:mm:ss")="15:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"23:20:00"),"Aft",IF(AND(TEXT( B8-INT(B8),"hh:mm:ss")="23:20:00",TEXT(B8-INT(B8),"hh:mm:ss")"07:20:00"),"Night",""))) I hope this helps Thanks John "John Calder" wrote: Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 11:20:00 PM to 7:19:00 AM should display the word NIGHT Assume the original time is in cell B8 Thanks John |
Thread Tools | |
Display Modes | |
|
|