need formula calculating time from the work sent

[zoharb]

Respected,
The user will insert current date & time in COL B using ctrl+: and ctrl+shift+:
The formula (in Col J) is calculating whether it is pending or not based on input given in COL G H I
We want to calculate time upto nearest hour based on date+time stamp added in COL B(I have used today() but other function can be used)
Zohar Batterywala

 

[zoharb]

formula used in calculation in COL J
=IF(AND(G2="digitizing",H2=""),"pending-"&(TODAY()-B2)*24&"hr",(IF(AND(G2="artwork",I2=""),"pending"&(TODAY()-B2)*24&"hr","")))

 

[Alex Blakenburg]

See if this gets you any closer. You indicated you always want to round UP.
Note:
• You are not displaying the Time in column B. This means that your hour calculation won't have any context and will be hard to understand
• If you are going down to the hour then should you be using Now() instead of Today()
• I would not put Today or Now in the formula. It means it is constantly changing and you can't view it at a particular point in time should you need to do so.
Put the Now() or Today() in a cell and refer to it. This will also help when you are testing.

=IF(OR(
       AND(G2="digitizing",H2=""),
       AND(G2="artwork",I2="")),
    "pending-"&TEXT(ROUNDUP((TODAY()-B2)*24,0),"##0")&" hrs",
    "")

 

[zoharb]

Hi Alex,
ISSUE is solved.
formula used --- =IF(AND(G2065="digitizing",H2065=""),"pending-"&ROUND((NOW()-B2065)*24,2)&"hr",(IF(AND(G2065="artwork",I2065=""),"pending"&ROUND((NOW()-B2065)*24,2)&"hr","")))
THANK YOU anyway for guiding me
HAPPY THANKSGIVING

 

[Alex Blakenburg]

You're welcome. Glad you found something that worked for you.
I would still suggest you would be better off following my approach, it requires less maintenance. You have currently got what should be an identical output in 2 places and in one you just have "pending" and the other you have "pending-". This will only get worse if you add more conditions.
I did think about using the LET function to save repeating the text but you have both versions 2021 & 2013 listed and LET would only work in 2021.