Confusing Conditional Date&Time formula/calculation

I

indrajeet_rajput

New Member
Joined
Sep 7, 2024
Messages
6
Office Version
  1. 365
Platform
  1. Windows
So, I have a date&time column in my raw data which I extract daily (the date&time refers to the audit date&time), along with a Feedback Status column(which can have only these three values Accepted/Rejected or Pending) We don't have any date/time for when the feedback was Accepted or Rejected....

I need to create a column for Ontime or Late Feedback acknowledgement where the condition is => if it has been over 48 hours from the Audit date&time and the Feedback Status is "Accepted"or"Rejected" it should return "Late Acknowledgement"; Or if it has been less than 48 hrs from the Audit date&time and the Feedback Status is "Accepted" or "Rejected" it should return "Ontime Acknowledgement"
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Maybe...

Book1
ABCDE
1date&time FeedbackOntime or Late Feedback
203/11/2024 23:00AcceptedOntime Acknowledgement
304/11/2024 11:00RejectedOntime Acknowledgement05/11/2024 17:32
403/11/2024 23:00PendingPending
504/11/2024 11:00PendingPending
603/11/2024 17:30AcceptedLate Acknowledgement
703/11/2024 11:00RejectedLate Acknowledgement
803/11/2024 17:30RejectedLate Acknowledgement
903/11/2024 17:35RejectedOntime Acknowledgement
Sheet3
Cell Formulas
RangeFormula
E3E3=NOW()
C2:C9C2=IF(B2="Pending","Pending",IF(AND(OR(B2="Accepted",B2="Rejected"),A2<=NOW()-2),"Late Acknowledgement","Ontime Acknowledgement"))


Edit:
or

Book1
ABCDE
1date&time FeedbackOntime or Late Feedback
203/11/2024 23:00AcceptedOntime Acknowledgement
304/11/2024 11:00RejectedOntime Acknowledgement03/11/2024 17:4005/11/2024 17:40
403/11/2024 23:00PendingPending
504/11/2024 11:00PendingPending
603/11/2024 17:30AcceptedLate Acknowledgement
703/11/2024 11:00RejectedLate Acknowledgement
803/11/2024 17:45RejectedOntime Acknowledgement
903/11/2024 17:35RejectedLate Acknowledgement
Sheet3
Cell Formulas
RangeFormula
D3D3=NOW()-2
E3E3=NOW()
C2:C9C2=IF(B2="Pending","Pending",IF(AND(OR(B2="Accepted",B2="Rejected"),A2<=$D$3),"Late Acknowledgement","Ontime Acknowledgement"))
 
Last edited:
Upvote 0
Actually as it doesn't make a difference if it is accepted or rejected then maybe

Book1
ABCDE
1date&time FeedbackOntime or Late Feedback
203/11/2024 23:00AcceptedOntime Acknowledgement
304/11/2024 11:00RejectedOntime Acknowledgement03/11/2024 18:0005/11/2024 18:00
4PendingPending
5PendingPending
603/11/2024 17:30AcceptedLate Acknowledgement
703/11/2024 11:00RejectedLate Acknowledgement
803/11/2024 18:45RejectedOntime Acknowledgement
903/11/2024 17:55RejectedLate Acknowledgement
Sheet3
Cell Formulas
RangeFormula
D3D3=NOW()-2
E3E3=NOW()
C2:C9C2=IF(B2="Pending","Pending",IF(A2<=$D$3,"Late Acknowledgement","Ontime Acknowledgement"))
 
Upvote 0
MARK858 said:
Maybe...

Book1
ABCDE
1date&time FeedbackOntime or Late Feedback
203/11/2024 23:00AcceptedOntime Acknowledgement
304/11/2024 11:00RejectedOntime Acknowledgement05/11/2024 17:32
403/11/2024 23:00PendingPending
504/11/2024 11:00PendingPending
603/11/2024 17:30AcceptedLate Acknowledgement
703/11/2024 11:00RejectedLate Acknowledgement
803/11/2024 17:30RejectedLate Acknowledgement
903/11/2024 17:35RejectedOntime Acknowledgement
Sheet3
Cell Formulas
RangeFormula
E3E3=NOW()
C2:C9C2=IF(B2="Pending","Pending",IF(AND(OR(B2="Accepted",B2="Rejected"),A2<=NOW()-2),"Late Acknowledgement","Ontime Acknowledgement"))


Edit:
or

Book1
ABCDE
1date&time FeedbackOntime or Late Feedback
203/11/2024 23:00AcceptedOntime Acknowledgement
304/11/2024 11:00RejectedOntime Acknowledgement03/11/2024 17:4005/11/2024 17:40
403/11/2024 23:00PendingPending
504/11/2024 11:00PendingPending
603/11/2024 17:30AcceptedLate Acknowledgement
703/11/2024 11:00RejectedLate Acknowledgement
803/11/2024 17:45RejectedOntime Acknowledgement
903/11/2024 17:35RejectedLate Acknowledgement
Sheet3
Cell Formulas
RangeFormula
D3D3=NOW()-2
E3E3=NOW()
C2:C9C2=IF(B2="Pending","Pending",IF(AND(OR(B2="Accepted",B2="Rejected"),A2<=$D$3),"Late Acknowledgement","Ontime Acknowledgement"))
Click to expand...
Awesome! Thank you so much! I used NOW() - A7 <= 2 for "On Time" and NOW() - A7 >= 2 for "Late Acknowledgement"

Also, if I keep now() in the formula instead of using a refrence cell like you did, will that affect anything or it doesn't matter?
 
Upvote 0
indrajeet_rajput said:
Awesome! Thank you so much! I used NOW() - A7 <= 2 for "On Time" and NOW() - A7 >= 2 for "Late Acknowledgement"
Click to expand...
I don't understand with your description in post 1 why you need 2 tests, it is a Boolean answer if it is not Ontime then it is late

indrajeet_rajput said:
Also, if I keep now() in the formula instead of using a refrence cell like you did, will that affect anything or it doesn't matter?
Click to expand...
Depends how many times you are using the formula. If you are only using the formula once then it makes no big difference (the Now in E3 is just there to show the Date/Time when I posted the screenshot, it isn't used in the formula. Only the =NOW()-2 in D3 is used)
If you are using the formula multiple times then Excel will have to calculate Now() for each individual cell if it is in the formula
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
If a cell contains the word "Completed", then return the date/time when it changed.
2
Replies
14
Views
751
dreid1011
dreid1011
L
  • Question Question
Determine match based on if a date range falls within a larger date range :S
Replies
1
Views
403
dermie_72
D
P
  • Question Question
Need suggestions on what to use for a real time tracker which auto refreshes the data every hour.
Replies
1
Views
298
Peter_SSs
Peter_SSs
A
  • Solved
count how many times text string appears in column
Replies
2
Views
176
ajm
A
P
  • Solved
Combining Date and Time
Replies
5
Views
240
AhoyNC
A

Forum statistics

Threads
1,223,577
Messages
6,173,164
Members
452,504
Latest member
frankkeith2233

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top