Using the IF function

[Maven4Champ]

Ok I am attempting to use an If fucntion similar to this

=IF(Cell<=0.29167, Cell+.5, Cell)

where as 0.29167 equals 7AM and the .5 changes from AM to PM.

Here is how the data appears in the spreadsheet

Time From: Time To:
11:00AM 11:30AM
11:30AM 12:00PM
12:00PM 12:30PM
12:30PM 1:00PM
1:00AM 1:30PM
1:30AM 2:00PM
2:00AM 2:30PM
2:30AM 3:00PM
3:00AM 3:30PM
3:30AM 4:00PM
4:00AM 4:30PM
4:30AM 5:00PM
5:00AM 5:30PM
5:30AM 6:00PM
6:00AM 6:30PM
6:30AM 7:00PM
7:00AM 7:30PM

Here is my problem. In the 'Time From' field, the times actually show up as AM starting at 1:00. So 1:00-7:00, or later if the report actualy needed, all show as AM value instead of PM value. Now what the above function would do is change anything with an equal or greater value of 0.29167(7:00:00AM) to PM by adding .5.

Here is my problem - some reports will contain 7:00AM purposely (i.e. the report time is from 7am-4pm), in which case this function I assume would also change the starting report time of 7am to a pm value as well - correct?

I believe I need the function to basically do the following:

If 'Time From' is greater or equal than 0.29167(7:00:00am) and 'Time To' has a value of PM, then add .5

Is something like that possible?

 

[Norie]

Is it actually a spreadsheet?

Try the other forum

Excel Questions

 

[Maven4Champ]

Is it actually a spreadsheet?

Try the other forum

Excel Questions

No, this is in Access - using the IF function...

 

[Maven4Champ]

anyone?

 

[Maven4Champ]

OK it comes up like this:

Expr1: IIf([Time From]<=0.29167 And [Time To]>0.5,[Time From]+0.5,[Time From])

I believe that is correct? Upon going in and testing it, it looks right!

Now I have another problem that has caused this however. What I have is 1 table and 1 query right?

In place of the Field name 'From Time' I put the above expression.

However, now in the query results, it shows up as Expr1 as the column title/header instead of saying 'From Time' - As well, everything is in decimals now - so what I did to fix that was to go back into the Design View, go to Properties for the Expr1 column and change it to MEDIUM TIME format - it now shows up right...

but what do I do about it being labeled as Expr1 now? Should I keep my normal query and then create a subquery to run this - but if so - how would the relationship work...

Before you answer again - I want to thank everyone SO SO SO SO SO much for all their help. This has been a really big challenge and I can be happy to tell my boss I have her database - but with some great help!

 

[Norie]

Just replacing 'Expr1' with 'From Time' should work to give you the column name you want.

 

[Maven4Champ]

Just replacing 'Expr1' with 'From Time' should work to give you the column name you want.

Well, when I apply this expression, the information in the query updates to the new format (i.e. the AM's are now PM's) but how do I make it update that information in the table?

Do I need to create an Update Table Query to do that and if so - - - how?