Time Formula

[johng41]

Hi

New to Excel so still learning :-(

I am trying to make a time card to keep track of hours worked., this is what i have so far

time card.JPG

In total hours i have =TEXT(E21-D21,"h:mm")

what i need is a formula so that if total hours is less than 6, then subtract 15 minutes from total hours, if more than 6 subtract 30 minutes.

I have =IF(F21<6,F21-0.25,F21-0.5) but it continues to give me a result of -0.15

Using office 365 (office 16)

many thanks

John

[FDibbins]

Hi, welcome to the forum
What you need to understand about dates and times in excel is...

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Tue Nov 2015) is actually 42332

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So, with that said, you need to test for 6/24 (or 0.25) and 15 mins =15/60/24

Also, unless you need to ad some actual text, you should try and avoid using TEXT() to "convert" values - they become (surprise, surprise) text and are no longer values that can be directly used in future formulas

[Sonia67]

Hi John,

Please find attached Spreadsheet with your example.

I have entered the formula into cell F6, it subtracts 15 mins if worked less than 6 hrs, and 30 mins if worked 6 or more hours.

I have also formatted the style of the date, so you can have a look.

I have also added in the IsBlank and IsError functions, so that any days not worked, will show as blanks.

Hope this is what you want.

Regards,

Sonia

[Sonia67]

Hi John,

Sorry, I just noticed a mistake in the formula, it was subtracting 15 mins where it should have subtracted 30 mins. I have adjusted the formula now.

Regards,

Sonia

[johng41]

Hi Sonia

Many many thanks for that

exactly what i was looking for

[Sonia67]

Your Welcome,

Glad to be of help.

Regards,

Sonia