Wanted to know if the function "If" works with Today()

[EricKamei]

I was working on one of the report so that the date automatically appear once we enter the time in another column for different shifts. Was not able to figure it out. Here is the what I was working on if someone could help would be great.

Shift - 1 8:00 PM - 5:00 AM
Shift - 2 4:30 AM - 1:30 PM

IF(AND(A1>="8:00:00 PM"+0,A1<="10:00:00 AM"+0),"Today()",IF(AND(A1>="04:00:00 AM"+0,A1<="2:00:00 PM"+0),"Today()"))

[Saarang84]

Hi,

Welcome to the Forum.

Assuming that the timings of shift 1 & 2 are in the range A1:B2, then I believe this the formula is what you are looking out for..

Please Login or Register  to view this content.

Format the cells where you use this formula as Date

[gak67]

Saarang84's formula is a more efficient version of yours, but yours would have worked if you took off the double quotes from around Today(). It is a function, so does not need the quotes. Your formula also doesn't have a result for if the condition in the second IF statement is not met, so it would probably return a FALSE response in that circumstance.

[EricKamei]

Appreciate your respond, Sarang. There was little confusion. I meant there are two shifts (8:00:00 PM to 5:00:00 AM and 4:30:00 AM to 1:30:00 PM). Every time they have worked on the issue within their shift (Shift-1 or Shift-2), they will have to enter the time in G4.
Example: 1st Issue work at 8:00:00 PM, 2nd Issue at 9:00:00 PM for the Shift - 1, 1st Issue for Shift -2 worked at 4:00:00 AM, 2nd Issue at 6:00:00 AM so on...

Every agents are using the same formatted excel file for their daily reports and I want the date to be automated because every one comes up with their own date format.

I've tried this way again after your suggestion but it comes the result as 00/01/00.

Here is the formula I've used: =IF(OR(AND(G4>="7:00:00 PM"+0,G4<="03:59:00 AM"+0),AND(G4>="04:00:00 AM"+0,G4<="2:00:00 PM"+0)),TODAY(),0)

Not sure if this would be possible

Thank you.

[Saarang84]

Hi Eric,

To fix up the formula for you, I would need to have a look at the layout of the worksheet that is being used by the agents. Can u upload a sample workbook with the layout that is being used (without insensitive data) ?

[EricKamei]

Hi Saarang,

I have uploaded the excel file. Hope this will help.

Thank you.

[Saarang84]

Hi Eric,

I believe what you need is a macro here to insert a Timestamp in appropriate columns in your agent workbook. You can click the Start/Stop button to do the same.

Before clicking this button, you need to place the cursor in appropriate chat start or chat end time column accordingly.

If TODAY() function is used in an if condition as you have asked for, it won't retain the previous values. In other words, for an agent who's tracking data in his worksheet everyday, the Date column needs to reflect the appropriate date. To extract the date from the chat time, I've incorporated the formula accordingly. You need to protect the chat start / end time columns for disabling the agent to manipulate the times (to prevent manually editing of the time values).

Use the below formula for Date (in col D ):

Please Login or Register  to view this content.

The below code places the current TIMESTAMP when the button is clicked.

Please Login or Register  to view this content.

This works in Excel 2003 version as well. Refer workbook attached.

[EricKamei]

Saarang,

You are great! Thank you so much. That work now. I think, I will need to learn some Visual Basic Editor or macros.