I’m trying to create a formula that would allow me to find the difference between a past date and today. If the number of days are 90 or more then the hours worked must be subtracted from the 60 hours allowed. If the number of days between those two dates is less than 90 the number of days needs to be multiplied by 2/3 an hour per day and then the subtract hours worked from this number to show the difference between the targeted hours for that date in the 90 day cycle and the amount of time worked. The formula I have is.
This is in Google Sheets.
Where “C” is the date the person started their 90 day test cycle, and “E” is the number of hours worked.
=If(Today()-$c5=>90;60:00:00-$e5;Today()-$C5*(2/3)-($E5*24))
I get a parse error.
I was hoping for a result that would look like this for date 9/19/24
A B C D E
Employee name Id Test Date Current hours worked Hours needed
jane doe 23 5/19/24 17:23 42:37
Joe Blow 24 6/19/24 4:12 55:48
Suzie Q 25 7/19/24 36:53 8:07
Dolly Madison 26 8/19/24 10:34 12:16
suz odo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
=If(Today()-$c5=>90;60:00:00-$e5;Today()-$C5*(2/3)-($E5*24))
That will get a parse error, because 60:00:00 does not compute in a formula without quotes. Assuming that cell E5 contains a duration rather than a plain number, try this:
=if(today() - $C5 => 90; "60:00" - $E5; today() - $C5 * (2 / 3) - ($E5 * 24))
See Working with date and time values in Google Sheets.